Skewy Date Logic

[kj27]

I am filtering records (Crystal XI) based on a SQL Server 2005 DateTime field (EST_PROPOSAL). So my formula is as follows:

((Month ({C_OPPORTUNITY.EST_PROPOSAL}) = {@CurrentMonth}) and ((Year ({C_OPPORTUNITY.EST_PROPOSAL}) = {@CurrentYear})))

The CurrentMonth and CurrentYear formulas are simply Month (CurrentDate) and Year (CurrentDate), respectively.

The filtering seems fine except for records where EST_PROPOSAL is set for the first day of the month. So if the value in the database is: 2011-10-01 00:00:05.000, it is actually being evaluated as: 9/30/2011 08:05:00 PM in Crystal. Ths is obviously causing records to be eroneously excluded.

I'm not quite sure what could be causing this issue and if there is a way around it.

Thanks

 

[jdemmi]

Is there any chance that your DB is storing date/time values in GMT/UTC format and/or the report (or application launching the report) is applying a GMT offset to the date/time value?

9/30/2011 8:05 PM EASTERN DAYLIGHT TIME is 10/1/2011 00:00:05 in GMT/UTC

(GMT -4)

-- Jason
"It's Just Ones and Zeros