dates within range not being detected

[Guest]

Iam not sure if this problem is with ColdFusion or if it is with SQL Server.

The following is my code:

<cfparam default="#DateAdd("d", -7, Now())#" name="attributes.StartDate">
<cfparam default="#DateAdd("d", 1, Now())#" name="attributes.EndDate">

<cfquery name="login_records" datasource="#application.datasource#" username="#application.username#" password="#application.password#">
  SELECT *
  FROM tblLoginRecords
  WHERE LoginDate
  BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#attributes.StartDate#">
  AND <cfqueryparam cfsqltype="cf_sql_date" value="#attributes.EndDate#">
  ORDER BY UserName ASC, LoginDate DESC
</cfquery>

Basically it is not returning dates at the end of the range. At first I thought it was because the EndDate date creation assumes a start time of 12am but even when I used createDateTime and set it for 23,59,59 it still wouldn't return the dates at the end of the range. Any help would be greatly appreciated.

Cheers

[tooMuchTrouble · Accepted solution]

i think you might want CF_SQL_TIMESTAMP instead of cf_sql_date.

[Guest]

Thanks heaps for that I was going mental trying to find a solution.