Request records between two dates
I have a page where I want to run some reports on ColdFusion and SQL Server database usage.
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 problem is that it dumps all records and doesn't apply the date filter. When I dump a query, it dumps all records to the database. It ignores the WHERE clause even if the SQL dump says:
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?
source to share
it dumps all records and doesn't apply a date filter.
It applies a date filter. This is simply not what you expected.
I suspect you were trying to find entries dated June 1 - June 8, 2017. However, if you take a close look at the generated sql, it actually filters from Jan 6 to Aug 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 US time conventions. , i.e. month. So when you pass in a string like "01/06/2017", it will be interpreted as January 6th - not June 1st. To properly handle strings other than US, either
-
Use language 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 an appropriate mask:
<cfset form.dFrom = "01/06/2017"> <cfset writeDump( ParseDateTime(form.dFrom, "dd/MM/yyyy") )>
Keep in mind that CF date functions are generally generous in what they consider to be correct date strings, so you might want to add additional validation.
Also, for performance reasons, always use cfqueryparam for any variable query parameters. More flexible approach for date matching:
WHERE started >= <cfqueryparam value="#someStartDate#" cfsqltype="cf_sql_date">
AND started < <cfqueryparam value="#dateAdd('d', 1, someEndDate)#" cfsqltype="cf_sql_date">
source to share
Further editing. The following code should work if 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>
source to share