Convert string to datetime in crystal reports

[elou]

Hi all,
I am working on a report that retrieve information from a database. In that database, the datetime is stored as a string. Is there any possible way I can convert it to datetime in the crystal reports?

Thanks a lot.

Lou

 

[vidru]

What's the format of the string? Depending on its String format, you can try using CDateTime in a formula. If it's not in an acceptable format, it'll let you know:

CDateTime({Table.Field})

-dave

 

[kskid]

A format of the string would be helpful.

If it's in the right format, then the CDate function may work. Otherwise, you will have to break down the string into numbers and apply the numbers in the datetime function

 

[synapsevampire]

You can convert it 2 ways:

A SQL Expression

A Crystal Formula

How one does so depends entirely on the format of how it's stored as a string, post specifics when requesting technical information:

Crystal version
Database/connectivity used
Example data
Expected output

The first thing to try in a Crystal formula is:

cdate({table.field})

If this fails, then you'll need to parse the data out, as in:

stringvar MyDateStr:= "01/01/1970";
datevar MyDate;
MyDate:= cdate(val(mid(MyDateStr,7,4)),val(mid(MyDateStr,4,2)),val(mid(MyDateStr,1,2)));
MyDate

So use the MID function to extract the elements required to fill the cdate function with the values (note they are converted to numeric in this example).

cdate uses (year,month,day)

If you still have difficulties, post technical information.

-k

 

[elou]

thanks for replying,
here is the format of the string:
2005-03-09~06:23:13~24~NULL

 

[synapsevampire]

That's bizarre, what does the 24 and null mean?

Anyway, try:

stringvar MyDateStr:= {table.field};
datetimevar MyDate;
MyDate:= cdatetime(val(mid(MyDateStr,1,4)),val(mid(MyDateStr,6,2)),val(mid(MyDateStr,9,2))
,val(mid(MyDateStr,12,2)),val(mid(MyDateStr,15,2)),val(mid(MyDateStr,18,2)));
MyDate

Replace {table.field} with your datetime string field.

-k

 

[elou]

Great!
it worked out perfectly.
Thank you very much.
Appreciate your help.