查询两个日期之间的记录。

3

我有一个页面,想要使用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> 
数据-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#">
然而问题是,它会输出所有记录,并且不应用日期筛选。当我倾销查询时,它会倾销数据库中的所有记录。尽管SQL转储说明了WHERE语句,但它忽略了该语句。
 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 
有什么想法吗?

当您执行查询转储时,它看起来像什么? - snackboy
当我转储查询时,它会转储数据库中的所有记录。即使SQL转储语句指定了WHERE语句,它也会忽略它: 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
你能试一下这个查询语句吗:从t_users表中选择id、type和started字段,其中started大于等于#dateformat(fromDate,'yyyy-mm-dd')#并且小于等于#dateformat(toDate,'yyyy-mm-dd')#,按照started字段排序。 - tech2017
1
不要使用字符串,而是使用日期对象。字符串含义模糊,可能导致意外结果。(尽管我会使用cfqueryparam)。“started”列的数据类型是什么?能否提供一些示例值?另外,正如丹所问的那样 - 在SSMS中运行查询时会发生什么? - Leigh
1
哦等等....DD/MM/YYYY将无法与标准日期函数一起按预期工作,例如createODBCDate/Time。标准函数仅支持美国日期约定,即先月份。要解析像dd/mm/yyyy这样的非美国格式,您需要使用LS(区域设置特定)函数,如LSParseDateTime()。 - Leigh
显示剩余6条评论
2个回答

6
它会输出所有记录,但不应用日期筛选器。 它确实应用了日期筛选器,只是不是您期望的那个。我猜想您试图找到2017年6月1日至6月8日之间的记录。然而,如果您仔细查看生成的SQL语句,它实际上是在2017年1月6日至8月6日之间进行过滤。 原因是标准CF日期函数只理解美国日期约定,即先月份。因此,当您传递类似“01/06/2017”这样的字符串时,它将被解释为1月6日,而不是6月1日。要正确处理非美国日期字符串,请
  • 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") )>
    
请记住,CF的日期函数通常对其认为有效的日期字符串非常慷慨,因此您可能需要添加一些额外的验证。 另外,出于性能原因,请始终在任何变量查询参数上使用cfqueryparam。日期比较的更灵活的方法是:
  WHERE started >= <cfqueryparam value="#someStartDate#" cfsqltype="cf_sql_date"> 
  AND   started < <cfqueryparam value="#dateAdd('d', 1, someEndDate)#" cfsqltype="cf_sql_date"> 

1
我喜欢那个日期处理方式。我想这不是我真正考虑的事情,但通常,在MS数据库中,你会使用cf_sql_timestamp。cf_sql_date截断时间并将其设置为午夜,而cf_sql_timestamp包括时间。小于第二天的午夜应该可以得到前一天的所有内容。尽管如果您在数据类型精度边界处具有秒数(即23:59:59.998),那么它可能会四舍五入到下一天并被排除在外。检查您的数据库列的数据类型。我以前提过,但我讨厌日期。 - Shawn
@Shawn - 自动截断是cf_sql_date的一项好功能之一。这使得构建整个>={dayAtMidnight} and < {nextDayAtMidnight}变得容易。是的,四舍五入很有趣,但是...不确定在这种情况下是否存在问题?我认为旧的datetime类型在插入/更新时会自动将最接近的增量四舍五入。因此,如果您使用了奇怪的值,例如x.998,那么数据和过滤器都将被舍入到相同的值(最接近的0.003或其他值)。 - Leigh
1
我曾经在一个系统中工作,该系统的事务日期精确到秒的 .xxx。有时候这很令人沮丧。一个在 xx.998 的交易会四舍五入到下一天,而 xx.002 会四舍五入到当天的开始。因此,虽然技术上来说 xx.998 对于我所寻找的那一天是有效的,但它会被四舍五入到下一天并被排除在外。真是太痛苦了。如果您不需要时间,请选择一个甚至不记录时间的数据库数据类型。如果您需要时间,请确保您有足够的精度来获得所需的内容,然后确保您在代码中使用正确的数据类型。 :-/ - Shawn
噢,是啊。如果你真的需要0.998,那么旧数据日期时间类型就无法满足你的需求了。处理日期时间总是那么有趣... - Leigh
SQL2K数据库,因此有许多有趣的小问题,现在已经好多了。我仍然不确定我们是否真的应该关心发生在13:25:47.998或13:25:47.150的事情。 :-) - Shawn
对于大多数只关心秒数的应用程序来说,可能并不是灾难性的,但这一切都取决于情况 :) - Leigh

0
进一步编辑。以下代码在包含为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>

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,