如何在 SQL Server 中比较日期

2022-01-09 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>

相关文章