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

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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!