×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Convert string to datetime in crystal reports

Convert string to datetime in crystal reports

Convert string to datetime in crystal reports

(OP)
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

RE: Convert string to datetime in crystal reports

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

RE: Convert string to datetime in crystal reports

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

RE: Convert string to datetime in crystal reports

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

RE: Convert string to datetime in crystal reports

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

RE: Convert string to datetime in crystal reports

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

RE: Convert string to datetime in crystal reports

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close