Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Topic: Convert a string to a date Posted: 09 Sep 2009 at 2:40pm
I have a date that is created by concatenating a string of 8 fields (totext({ValYSSF_.F109})+totext({ValYSSF_.F110})+totext({ValYSSF_.F111})+totext({ValYSSF_.F112})+totext({ValYSSF_.F105})+totext({ValYSSF_.F106})+totext({ValYSSF_.F107})+totext({ValYSSF_.F108}) and it returns something like 05272000. How can I convert that result into a date so I can do a datediff with it?
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Posted: 09 Sep 2009 at 3:18pm
I've got it working but we have some dates that were entered as 99/99/9999 for @DOB and Crystal doesn't like them in the formula
DateDiff ("d", ({@DOB}), ({@SrvyDate})) Can you think of a way to disregard any dates like that so it doesn't even do the calculation on them? This data set stinks!
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Posted: 10 Sep 2009 at 8:36am
I tried that and I'm getting an error that says "The date date time literal was not understood" Crystal can't even evaluate the the @DOB if it would return 99/99/9999. GRRRR
Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Posted: 10 Sep 2009 at 9:09am
Date(totext({ValYSSF_.F109})+totext({ValYSSF_.F110})+"/"+totext({ValYSSF_.F111})+totext({ValYSSF_.F112})+"/"+totext({ValYSSF_.F105})+totext({ValYSSF_.F106})+totext({ValYSSF_.F107})+totext({ValYSSF_.F108}))
What I think I'm going to do is concatenate the string first in a separate equation, then do an if then that checks for the the 99/99/9999 before it converts it to a date
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 10 Sep 2009 at 9:18am
msnoshoes....I assume these are numeric fields you are using. NUmeric Totext often leaves decimals which is messing you your conversion. to test remove the DATE() function and just look at the string. If so use the following to remove the decimal and 00...
I could be wrong but I think crystal will choke on that formula. Your output has to be consistent in what is returns so you need to return a date instead of "".
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum