Query records between 2 dates

Question

I have a page that I want to run some reports on using ColdFusion and a SQL Server database.

Here is my form:

<cfform name="dateRange" action="" method="POST">

   <label>Date From</label><br>
   <cfinput type="DateField" name="dFrom"  mask="DD/MM/YYYY">

   <label>Date To</label><br>
   <cfinput type="DateField" name="dTo" mask="DD/MM/YYYY">

   <cfinput type="submit" value="Submit" name="Submit">
</cfform>

<hr>

<cfif isDefined("form.submit")>
   <cfinclude template="data-p.cfm">
</cfif> 

The data-p.cfm file looks like this:

<cfset fromDate = #CREATEODBCDATETIME(#form.dFrom#)#>
<cfset toDate = #CREATEODBCDATETIME(#form.dTo#)#>

<cfquery name="t">
    SELECT id, type, started 
    FROM   t_users 
    WHERE  started >= #fromDate# 
    AND    started <= #toDate# 
    ORDER  BY started
</cfquery>

<cfdump var="#t#">

However the issue is that it dumps out all of the records and doesn't apply the date filter. When I dump the query it dumps all the records in the DB. It ignores the WHERE statement even though the SQL dump states:

 SELECT id, type, started 
 FROM   t_users 
 WHERE  started >= {ts '2017-01-06 00:00:00'} 
 AND    started <= {ts '2017-08-06 00:00:00'} 
 ORDER BY started 

Any ideas?

Answer 1

it dumps out all of the records and doesn't apply the date filter.

It does apply a date filter. It is just not the one you expected.

I suspect you were trying to find records dated between June 1 - June 8, 2017. However, if you look closely at the generated sql, it is actually filtering on January 6 - August 6, 2017.

where started >= {ts '2017-01-06 00:00:00'} and started <= {ts '2017-08-06 00:00:00'}

The reason is that the standard CF date functions only understand U.S. date conventions, i.e. month first. So when you pass in a string like "01/06/2017", it will be interpreted as January 6th - not June 1st. To handle non-US date strings correctly, either

• Use locale sensitive functions such as LSParseDateTime() (with the appropriate locale). For example:

  <cfset form.dFrom = "01/06/2017">
  <cfset writeDump( LSParseDateTime(form.dFrom, "de_DE") )>
  

• Or for numeric dates, use ParseDateTime() with the appropriate mask:

  <cfset form.dFrom = "01/06/2017">
  <cfset writeDump( ParseDateTime(form.dFrom, "dd/MM/yyyy") )>
  

Keep in mind CF's date functions are notoriously generous in what they consider valid date strings, so you may want to add some additional validation.

Also, for performance reasons, always use cfqueryparam on any variable query parameters. A more flexible approach for date comparisons is:

  WHERE started >= <cfqueryparam value="#someStartDate#" cfsqltype="cf_sql_date"> 
  AND   started < <cfqueryparam value="#dateAdd('d', 1, someEndDate)#" cfsqltype="cf_sql_date">