2005年MS SQL的日期比较。

I have an HTML form where I ask the user to select dates and then hit submit. Upon submitting, I am trying to fetch records between the selected dates using Coldfusion. My query looks like this:

我有一个HTML表单，我要求用户选择日期，然后单击submit。在提交时，我尝试使用Coldfusion在选定日期之间获取记录。我的查询是这样的:

    SELECT * FROM user_activation_events
where  STATUS_CODE =1
AND  event_date >= #Dateformat(form.from_date, 'dd-mm-yyyy')#
AND  event_date <= #Dateformat(form.to_date, 'dd-mm-yyyy')#

But this does not work as the date is stored in the database in this format: yyyy-mm-dd hh:mm:ss

但是，当日期以这种格式存储在数据库中时，这个方法就不起作用了:yyyy-mm-dd hh:mm:ss。

Can somebody please tell me how to do it?

谁能告诉我怎么做吗?

2 个解决方案

#1

There are a couple of issues going on here. Any form field that ends in _date is a form validation criteria. So the form field needs to be renamed todate and fromdate. Next it is good that you are trying to sanitize the input. cfqueryparam is used to do that. Last but not least, between is cleaner SQL Your query should look a little like:

这里有几个问题。任何以_date结尾的表单字段都是表单验证标准。因此，需要将表单字段重命名为todate和fromdate。接下来，最好尝试对输入进行消毒。cfqueryparam用于此。最后但并非最不重要的是，between is clean SQL您的查询应该看起来像:

<cfif isDate(form.fromDate) AND isDate(form.toDate)>

    <cfquery name="qryUser_Activation_Events">
    SELECT * 
    FROM   user_activation_events
    WHERE  STATUS_CODE =1
    AND    event_date BETWEEN <cfqueryparam cfsqltype="CF_SQL_date" value="#form.fromDate#">
        AND DATEADD(d, 1, <cfqueryparam cfsqltype="CF_SQL_date" value="#form.toDate#">)
    ORDER BY ...
    </cfquery>

<cfelse>  
    <!--- Error handling goes here --->
</cfif>

#2

As suggested in the comments, a more flexible way to filter the dates is using this approach. It works regardless of whether the column contains dates only or dates and times and will not interfere with the database's use of indexes (as using date functions may).

正如评论中所建议的，一种更灵活的筛选日期的方法是使用这种方法。不管列是否只包含日期或日期和时间，它都可以工作，不会影响数据库对索引的使用(就像使用date函数那样)。

    WHERE  TheDateColumn >= TheStartDateAtMidnight         
    AND    TheDateColumn < TheDayAfterEndDateAtMidnight   

For example, if you wanted to return all records dated any time between 12/3 and 12/4/2012:

例如，如果您想要返回12/3和12/4/2012之间任何时间的所有记录:

<!--- omitted date validation for brevity ---> 
<cfset form.from_date = "12/03/2012">
<cfset form.to_date   = "12/04/2012">

<cfquery name="getEvents" datasource="#dsn#">
    SELECT  event_date
    FROM    user_activation_events 
    WHERE   event_date >= <cfqueryparam value="#form.from_date#" cfsqltype="cf_sql_date">
    AND     event_date < <cfqueryparam value="#dateAdd('d', 1, form.to_date)#" cfsqltype="cf_sql_date">
    AND     status_code = 1 
</cfquery>

Sample Data

样本数据

    2012-12-02 23:59:59.000
    2012-12-03 00:00:00.000
    2012-12-03 07:34:18.000
    2012-12-04 13:34:18.000
    2012-12-04 23:59:59.000
    2012-12-05 00:00:00.000

Results:

结果:

    1 | 2012-12-03 00:00:00.0  
    2 | 2012-12-03 07:34:18.0  
    3 | 2012-12-04 13:34:18.0  
    4 | 2012-12-04 23:59:59.0  

Note about dates:

But this does not work as the date is stored in the database in this format: yyyy-mm-dd hh:mm:ss

但是，当日期以这种格式存储在数据库中时，这个方法就不起作用了:yyyy-mm-dd hh:mm:ss。

It really has nothing to do with format. Datetime values are not stored the way you see them on screen. The yyyy-mm-dd hh:mm:ss is a user friendly string presented by whatever IDE you are using, and it can vary. Normally, datetime values are actually stored as numbers. The number typically represents an offset from some base date or epoch. In CF/java it is the number milliseconds since the unix epoch. So while your IDE may show you a human friendly date string like yyyy-mm-dd hh:mm:ss, internally it is just a number.

它真的和格式无关。Datetime值的存储方式与您在屏幕上看到的不同。yyyy-mm-dd hh:mm:ss是一种用户友好的字符串，由您所使用的任何IDE提供，并且它可以变化。通常，datetime值实际上存储为数字。数字通常表示来自某个基准日期或历元的偏移量。在CF/java中，它是自unix时代以来的毫秒数。所以，虽然你的IDE可能会显示一个友好的人类日期字符串，比如yyyyy -mm-dd:mm:ss，但在内部它只是一个数字。

Think of date queries as like any numeric comparison. If the query does not return all of the records you are expecting, it is usually because one of the numbers you are passing in is too large or small.

可以将日期查询视为任何数字比较。如果查询没有返回您所期望的所有记录，通常是因为您传入的数字中有一个太大或太小。

 WHERE Col >= 1354510800000   // java.util.Date => {ts '2012-12-03 00:00:00'}
 AND   Col <= 1354683600000   // java.util.Date => {ts '2012-12-05 00:00:00'}