Convert String to Date where invalid dates exist in DB 1

[christinh]

I am trying to convert dates stored as strings to a crystal date format. The problem is that the database I am using allows invalid / partial dates. For example, there are several records in my database that have a date of 19000000(year 1900 but the month and day unknown -- the month and day can also be unknowns in my database). Can anyone help. Thank you in advance!
Christin

 

[kenhamady]

What date do you want to print for the records that have an invalid date? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[christinh]

If the month is unknown, January would be fine. If the day is unknown, the first of the month. If both are unknown -- Jan., 1st. (The year is always available.)

This is really stumping me. Thanks!

 

[kenhamady]

Create a separate formula for each piece (Yr, Mth, day). For month it might be:

Val({date.field} [5 to 6])

Now when you put those three fields together as a date, instead of simply:

Date ({@year}, {@month}, {@day} )


You could use:

Date ({@year},

if {@month} in 1 to 12
then {@month}
else 1 ,

if {@day} in 1 to 31
then {@day}
else 1 ) Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[christinh]

Thank You! It worked great!