Date(number) function when not starting 12/30/1899 1

[ikards]

I have a date field in my SQL db that is a number - not a date/time. I assumed that I could simply use the Date(number) function (number of days starting from Dec. 30, 1899), but my dates were reported as all in 2101! I contacted the db vendor and they informed me that their number date was the number of days starting from Dec. 20, 1800 -- "Clarion" format. How do I use the Date(number) function when "the number of days starting from" is different than what CR is expecting? Thanks for your help!

-- Steve Ikard

 

[dgillz]

This is a null date that has been converted to a default date. This is a crystal reports option that needs to be turned off. Click on file, options, reporting tab and make sure the "convert null field values to default" is NOT checked. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[rogar]

To convert your "Clarion" date to a Crystal date, use this formula:

 DateAdd("d", {ClarionDate}, Date(1800, 12, 20))

This will calculate the true date that is {ClarionDate} days after the Clarion zero date. (Incidentally, the Clarion date formula was chosen so that {ClarionDate} Mod 7 would be the day of the week, Sunday being 0)