Convert a 'string' to full Date and Time

I am working with Crystal reports 2011 and have a string, which is in the following format - '11/26/2013 7:15:11AM' which I want to convert to a DateTime holding the same format including the (AM/PM)
John-S PretoriusTechnical Service Manager, Mid-AtlanticAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
vastoConnect With a Mentor Commented:
DateTimeValue({Table.Field})

if this doesn't work try to parse it like this:

DateTime({Table.Field})[7 to 10], {Table.Field})[1 to 2], {Table.Field})[4 to 5] ..... hour ... minute ... sec)
the second approach might be trivy because you may have 1 or 2 characters for month, day and hour . It might be better if you parse by "/" and ":" and combine the values using DateTime
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
This looks good - can I ask if there is a way 'trap' (If statement) that would avoid 'Bad date-time format string' as the current string I'm trying to convert also has some values that cannot be converted to date and time.
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
I found the statement that works : if isdate({table.field}) then DateTimeValue({Table.Field})

Thank you for your help.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for johnsp1234's comment #a39683894

for the following reason:

I found the statement that works : if isdate({table.field}) then DateTimeValue({Table.Field})

Thank you for your help.
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
I found the statement that works : if isdate({table.field}) then DateTimeValue({Table.Field})

Thank you for your help.
0
 
mlmccCommented:
You can do 2 checks

First for NULL - no value then for IsDateTime

If IsNull({DateString}) then
    Date(1900,1,1)
Else If IsDateTime({DateString}) then
    cDateTime({DateString})
Else
    Date(1900,1,1)


If it doesn't like your date format then you will have to use the second version with a minor adjustment.  The values passed must be numbers not a number string

DateTime(Val({Table.Field})[7 to 10]), Val({Table.Field})[1 to 2]), Val({Table.Field})[4 to 5]) ..... hour ... minute ... sec)


mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.