如何比较 SQL Server 中的日期

2022-01-01 00:00:00 sql sql-server-2005 sql-server coldfusion

我有一个 HTML 表单，我要求用户在其中选择日期，然后点击提交.提交后，我尝试使用 ColdFusion 获取所选日期之间的记录.我的查询如下所示:

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:

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

谁能告诉我怎么做?

推荐答案

这里有几个问题.任何以 _date 结尾的表单域都是表单验证标准.所以表单域需要重命名为todate和fromdate.接下来，您尝试清理输入是件好事.cfqueryparam 用于做到这一点.最后但并非最不重要的是，两者之间是更清晰的 SQL 您的查询应该看起来像:

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:

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

相关文章