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?

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` — Sam Allen, Jun 09 '17 at 14:12
What is an example of a `started` value outside that range that gets returned in the results? Also, what happens when you run that sql from ssms? — Dan Bracuk, Jun 09 '17 at 14:26
can you try: select id, type, started from t_users where started >= #dateformat(fromDate,'yyyy-mm-dd')# and started <= #dateformat(toDate,'yyyy-mm-dd')# ORDER BY started — tech2017, Jun 09 '17 at 14:31
No. Stick with date objects. Strings are ambiguous and can lead to unexpected results. (Though I would use cfqueryparam). What is the "started" column data type and example of some of the values? Also, as Dan asked - what happens when you run the query in SSMS? — Leigh, Jun 09 '17 at 14:59
Oh wait.... DD/MM/YYYY will not work as expected with the standard date functions, like createODBCDate/Time. The **standard functions only support U.S. date conventions**, ie month first. To parse non-US formats like dd/mm/yyyy, you need to use LS (Locale Specific) functions like [LSParseDateTime()](https://www.pwrc.usgs.gov/cfdocs/htmldocs/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-70e3.html). — Leigh, Jun 09 '17 at 15:02
@Leigh The data in the DB is in US format. Its only the mask that is dd/mm/yyyy. The started column type is 'date' with a default value of getDate() — Sam Allen, Jun 09 '17 at 15:20
@SamAllen - Then that is the issue. CreateODBCDateTime assumes the input is in mm/dd/yyyy format. The form fields are in dd/mm/yyyy. So the function returns the wrong date. Notice your query is searching for January 6th through August 6th instead of searching for June 1st through June 8th? — Leigh, Jun 09 '17 at 15:38
It's a bit overkill for this single situation, but if you have need of multiple queries that involve dates, I would highly recommend looking into building a date dimension table. I tend to add them to almost all databases I work with, and I find them extremely useful. https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ — Shawn, Jun 09 '17 at 18:48
Also keep in mind that the way you have your forms written, if you were searching for 6/1 to 6/8, you will not include 6/8 in your results if there is ever a time component on your dTo column and it is after midnight. — Shawn, Jun 09 '17 at 18:53
Very true. See the example at the end of [my answer](https://stackoverflow.com/a/44461635/104223) for one way to avoid that problem. — Leigh, Jun 09 '17 at 19:17

Leigh · Answer 1 · 2017-06-09T18:11:31.957

6

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">

edited Jun 09 '17 at 18:11

answered Jun 09 '17 at 15:31

Leigh

28,765
10
55
103

1

I like the date handling on that. I guess it's not something that I really think about any more, but usually, you'd use a cf_sql_timestamp in a MS database. cf_sql_date truncates the time and sets it to midnight, whereas cf_sql_timestamp includes the time. And less than midnight of the next day should get you all of the previous day. Although if you do have seconds at the boundaries of the datatype precision (ie 23:59:59.998), that can round up to the next day and get excluded. Check the datatype of your database column. I've mentioned before, but I hate dates. – Shawn Jun 09 '17 at 20:04
@Shawn - The auto-truncate is one of the nice things about cf_sql_date. Makes it easy to construct the whole `>= {dayAtMidnight} and < {nextDayAtMidnight}`. Yeah, rounding is fun but ... not sure it is an issue in this case? I think the old datetime type auto rounds to the closest increment when inserting/updating anyway. So if you did use a weird value like x.998, both the data and filter would get rounded to the same thing, value (closest .003 or whatever). – Leigh Jun 09 '17 at 20:34
1

I used to work in a system that had transactional datetimes down to the .xxx of a second. It was frustrating at times. A transaction that came in at xx.998 would round up to next day, xx.002 would round down to the beginning of the day. So while technically a xx.998 would be valid for the day I was looking for, it would round up to the next day and be excluded. A pain. If you don't need time, choose a database datatype that doesn't even record it. If you do, make sure you have enough precision to get what you need, then make sure you use the right datatype in your code. :-/ – Shawn Jun 09 '17 at 21:04
Oh yeah. If you really needed .998, you are hosed with the old data datetime type. Such fun with dates... – Leigh Jun 09 '17 at 21:23
SQL2K db, so lots of fun little quirks that are _much_ better now. I'm still not sure if we really should have cared that something happened at 13:25:47.998 or at 13:25:47.150. :-) – Shawn Jun 09 '17 at 21:32
Probably not catastrophic for most apps, which only care about seconds, but .. all depends :) – Leigh Jun 10 '17 at 23:17

BKBK · Answer 2 · 2017-06-13T19:31:27.600

A further edit. The following code must surely work when included as data-p.cfm:

<cfset fromDay = listGetAt(form.dFrom, 1, "/")>
<cfset fromMonth = listGetAt(form.dFrom, 2, "/")>
<cfset fromYear = listGetAt(form.dFrom, 3, "/")>
<cfset ToDay = listGetAt(form.dTo, 1, "/")>
<cfset ToMonth = listGetAt(form.dTo, 2, "/")>
<cfset ToYear = listGetAt(form.dTo, 3, "/")>

<cfset fromDate = createdate(fromYear,fromMonth,fromDay)>
<cfset toDate = createdate(ToYear,ToMonth,ToDay)>

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

Query records between 2 dates

2 Answers2