Dates issue in Microsoft Dynamics CRM 4.0 Report

Dates issue in Microsoft Dynamics CRM 4.0 Report

I came across a problem today with a report I am writing in Microsoft Dynamics CRM using Report Builder 2.0

In my report I am taking user submitted Parameter of data type Date/Time and searching for transactions in CRM which are between the two dates specified using the following query

Declare @start datetime, @end datetime

Set @start = '2011-03-30'

Set @end = '2011-03-30'

SELECT

@start as StartDate ,@end as EndDate,

FilteredAp_transaction.ap_date,

FilteredContact.lastname,

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

FROM         FilteredAp_transaction INNER JOIN

FilteredContact ON FilteredAp_transaction.ap_contactnameid = FilteredContact.contactid

where

FilteredAp_transaction.ap_date

between @start and @end

What I found was that the report was not bringing in the expected results

In SQL dates are stored as 2011-01-13 10:08:20.000 in the format YYYYMMDD hh:mm:ss[.mmm]

Example

In the interest of simplicity let’s assume we are looking for all transactions on the same day

@Start = 2011/03/30

@End = 2011/03/30

Running the following Query gives 8 results.  However I knew this was incorrect.

After some investigation I realised it was the time portion of the Date/Time data type that was causing issues.

By adding the following line (Thank you www.bennadel.com)

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

I now have 9 results.  The reason for this is because the variables in the report are of type, Date/Time.  As well as the query comparing the date, it is also comparing the time value.  As I had a time value of 15:49:33.000 this was not being included in the results.  The above CAST statement strips the time value and replaces it with 00:00:00.000

We now go from a value of 2011-03-30 15:49:33.000 to 2011-03-30 00:00:00.000

Declare @start datetime, @end datetime

Set @start = '2011-03-30'

Set @end = '2011-03-30'

SELECT

@start as StartDate ,@end as EndDate,

FilteredAp_transaction.ap_date,

FilteredContact.lastname,

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

FROM         FilteredAp_transaction INNER JOIN

FilteredContact ON FilteredAp_transaction.ap_contactnameid = FilteredContact.contactid

where

CAST(FLOOR( CAST( FilteredAp_transaction.ap_date AS FLOAT ) )AS DATETIME)

between @start and @end