How to compare dates in SQL Server

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

Can somebody please tell me how to do it?

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

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

Solution 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).

    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:

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

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.

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