Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Convert a string to a date Post Reply Post New Topic
Page  of 3 Next >>
Author Message
rkorb
Newbie
Newbie
Avatar

Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Quote rkorb Replybullet 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?
As always, thanks for the help!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Sep 2009 at 2:51pm
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})
)
IP IP Logged
rkorb
Newbie
Newbie
Avatar

Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Quote rkorb Replybullet Posted: 09 Sep 2009 at 2:54pm
You always come though for me!  Thanks! 
IP IP Logged
rkorb
Newbie
Newbie
Avatar

Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Quote rkorb Replybullet 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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Sep 2009 at 4:51pm
You have to have it return a numeric value but you can make it return a specific value like 0...
if {@DOB}= #99/99/9999# then 0 else DateDiff ("d", ({@DOB}), ({@SrvyDate})) 
IP IP Logged
rkorb
Newbie
Newbie
Avatar

Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Quote rkorb Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Sep 2009 at 8:40am
I wondered how that was not crashing your dob formula....
what is your @DOB formula?


Edited by DBlank - 10 Sep 2009 at 8:41am
IP IP Logged
rkorb
Newbie
Newbie
Avatar

Joined: 04 Sep 2009
Location: United States
Online Status: Offline
Posts: 31
Quote rkorb Replybullet 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 
if {@DateString} = "99/99/9999 then "" else date({@DateString})
IP IP Logged
msnoshoes
Newbie
Newbie


Joined: 10 Sep 2009
Location: United States
Online Status: Offline
Posts: 9
Quote msnoshoes Replybullet Posted: 10 Sep 2009 at 9:11am
I seem to be having a similar problem and can't figure out what I am doing wrong. 
Date(totext({LECUSD.CUS_ARR_DATE_MM})+"/"+totext({LECUSD.CUS_ARR_DATE_DD})+"/"+totext({LECUSD.CUS_ARR_DATE_CC})+totext({LECUSD.CUS_ARR_DATE_YY}))
 
When I run a query using between 08/01/2009 and 08/02/2009 I get the error "Bad Date Format String.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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...
Date(totext({LECUSD.CUS_ARR_DATE_MM},0)+"/"+totext({LECUSD.CUS_ARR_DATE_DD},0)+"/"+totext({LECUSD.CUS_ARR_DATE_CC},0)+totext({LECUSD.CUS_ARR_DATE_YY},0))
 
Rkorb, 
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 "".
Try:
if {@DateString} = "99/99/9999 then #01/01/1900# else date({@DateString})
You can suppress this conditionally to amke it appear blank.
 
In your Datediff account fot that alteration as:
if {@DOB}= #01/01/1900# then 0 else DateDiff ("d", ({@DOB}), ({@SrvyDate})) 
 
Does that work?
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		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



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.