Converting SQL date/time string to Crystal date/time - how?? 2

[Drakhan]

How would I go about converting a SQL 7.0 string field that contains a date and time string? The field is called {USERLOG.LOGIN}, an example of the data looks like this:

20020315 13:04

What I would like to do change this to (eventually):

03/15/2002 1:04 PM

How can this be done? I am a technical person, but not the greatest programmer. Could someone please provide a coding example, or point me in the right direction? Any help is greatly appreciated. Thank you!

I have tried some of the formulas like IsDateTime (which returns FALSE), DateTimeValue (which returns 'Bad date-time format string' error), and a some others without much luck....

 

[chris777]

There may be a way to do this with the Picture function but I'm not sure what it would be. %- I CAN tell you the long way...

numbervar yr:=left({USERLOG.LOGIN},4) //extract the year
numbervar mn:=mid({USERLOG.LOGIN},5,2)//extract the month
numbervar d:=mid({USERLOG.LOGIN},7,2)//extract the day
numbervar hr:=right({USERLOG.LOGIN},4,2)//extract the hour
numbervar min:=right({USERLOG.LOGIN},2)//extract the minutes

//reassemble the whole thing

if hr>12 then
mn + "/" + d + "/" + yr + " " + (hr-12) + ":" + "min" + " PM"

else
mn + "/" + d + "/" + yr + " " + hr + ":" + "min" + " AM"


Assuming I havn't made any typos that should work for you.
Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[Naith]

whileprintingrecords;
numbervar yr;
numbervar mn;
numbervar dy;
numbervar tm1;
numbervar tm2;
numbervar tm3;
yr := tonumber((mid({@time},5,4)));
mn := tonumber((mid({@time},3,2)));
dy := tonumber((mid({@time},1,2)));
tm1 := tonumber((mid({@time},9,2)));
tm2 := tonumber((mid({@time},11,2)));
tm3 := tonumber((mid({@time},13,2)));

datetimevalue(yr,mn,dy,tm1,tm2,tm3)

Naith

 

[Naith]

Didn't realise Chris had already sorted you out. His solution should be just fine for you. Only difference is the last solution will force Crystal to recognise the formula as a datetype if you reference it anywhere else in the report.

The formula was copied from one of my previous examples which looks at a ddmmyyyyhhmmss string. So, you'll need to switch the mid positions of the ddmm and yyyy values.

Naith

 

[Drakhan]

WOW!! Thanks for the quick replies people!!! Great!! I'll try both...thanks again!!!

 

[synapsevampire]

If performance is a concern, either manipulate the string or change the datatype to a date in the table/View/SP you're getting the data from.

From within Crystal you can manipulate the field using a SQL Expression with the SQL Server *Substring* function, or you could even do a:

Convert(datetime,{USERLOG.LOGIN})

This changes it to a date, offloading the work to the database rather than having Crystal do it. (This might require some minor string formatting to work properly, I don't have SQL Server at this office)

To manipulate the string as in your previously received examples: (I basterdized Chris's example here):

substring({USERLOG.LOGIN},1,4) +
substring({USERLOG.LOGIN},5,2) +
substring({USERLOG.LOGIN},7,2) +
substring({USERLOG.LOGIN},9,2) +
substring({USERLOG.LOGIN},11,2)

I'd suggest changing the date type and fomat it however you like within CR using *format field*. If you're doing any date grouping, or date functions, you'll have to convert it anyway, the above string won't sort correctly at the time level, and maintaining the proper data type is generally beneficial.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lyanch]

I have a whole database full of datetimes as strings so I am well versed in your dilema. The above answers will work great as long as a) the date string is ALWAYS a date (ie no bad data) and b) the datestring is always there (no NULLS).

If you have the possibility of either you need to change the method a bit.

If you have a possibility of bad data, you probably don't want to use the SQL statement. So far crystals answer to the SQL error I get from sybase when the date is bad is to crash. In any case, you will get no data back because of the SQL error. The crystal conversions will work but you will need to do checks for string length for your extractions and the test to see if the string actually makes a date (isdate function).

If you can have nulls, you can use all the above but will need to add the check for null.

Lisa

 

[synapsevampire]

Good points, Lisa. I hadn't thought to mention checks for data integrity. You should have expanded this to include the CR solution suggested as well, which is also applicable.

However I disagree that the possibility of bad data precludes using a SQL Expression much more than it would a CR formula. It should, in most instances, be a benefit to determine that bad data exists, and you can allow for bad data within the SQL Expression or CR formula and serve 2 purposes: Correct or omit the data for the report and identify what needs to be corrected.

My experience with SQL Expressions is that basically anything you can do with SQL at the column Select point works, meaning functions, case, etc. With Oracle, it's a bit more problematic, but SQL Server Expressions seem more forgiving (read: generally work ).

It's more effort, and should only be a consideration if performance is important.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com