sql - 查询2个日期之间的记录

Question

我有一个页面，我想运行一些关于使用 ColdFusion 和 SQL Server 数据库的报告。

这是我的表格:

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

data-p.cfm 文件如下所示:

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

但问题是它转储了所有记录并且不应用日期过滤器。当我转储查询时，它会转储数据库中的所有记录。它会忽略 WHERE 语句，即使 SQL 转储指出:

 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 

有什么想法吗？

Answer 1

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

它确实应用了日期过滤器。这不是您所期望的。

我怀疑您正在尝试查找日期为 2017 年 6 月 1 日至 6 月 8 日之间的记录。但是，如果您仔细查看生成的 sql，它实际上是在 2017 年 1 月 6 日至 8 月 6 日期间进行过滤。

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

原因是标准 CF 日期函数只理解美国日期约定，即月份优先。因此，当您传入类似“01/06/2017”的字符串时，它将被解释为 January 6 日 - 而不是 6 月 1 日。要正确处理非美国日期字符串，要么

• 使用区域设置敏感函数，例如 LSParseDateTime() (具有适当的语言环境)。例如:

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

• 或者对于数字日期，使用 ParseDateTime()使用适当的面具:

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

请记住，CF 的日期函数在他们认为有效的日期字符串方面非常慷慨，因此您可能需要添加一些额外的验证。

此外，出于性能原因，始终使用 cfqueryparam在任何可变查询参数上。一种更灵活的日期比较方法是:

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