asked on

Report date against dateserial

Hello,

I am working on a Crystal Report that a user must enter a parameter for date. The problem is the date in the table is in date serial format. To make it easier for the user I have changed the format to show date but I am unable to get the report to show data for the requested date because the parameter needs a date serial. I have tried finding functions to convert a date entered by a user to date serial but was unable to find such a function. What is the way to accomplish this? Thanks

Mike McCracken

Is date serial a number like 200800715?

Try this formula

Year({?DateParameter}) * 10000 + Month({?DateParameter}) * 100 + Day({?DateParameter})

mlmcc

itsecurityteam

ASKER

No the date serial is like this 39507 = 02/29/2008. What my limited understanding of this is that 1 = 01/01/1900 and this number will continue up. I need to convert the date that the user enters in {?DateParameter} and convert it back to this number (example 39507) so the query can complete.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

itsecurityteam

ASKER

I tried the datediff and I was able to do the query however my query was off by two days so I added +2 like the following:

DateDiff('d',Date(1900,01,01),{?YourParameter}) + 2

By doing it this way will it affect other queries in the future. Maybe leap year or something like that?

Mike McCracken

Could be. I noticed that also when i tested 39507 with DateAdd and got 2 Mar rather than 29 Feb.

mlmcc

itsecurityteam

ASKER

Thank you!