How to compare dates in SQL Server

Question

I have an HTML form where I ask the user to select a date, and then click "Submit." After submitting, I try to get records between 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, since the date is stored in the database in the following format:

    yyyy-mm-dd hh:mm:ss

Can someone please say how to do this?

Answer 1

. , _date, . , todate fromdate. , . cfqueryparam . , : 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>

Answer 2

, . , ( ).

    WHERE  TheDateColumn >= TheStartDateAtMidnight         
    AND    TheDateColumn < TheDayAfterEndDateAtMidnight   

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

    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

:

    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  

:

, : yyyy-mm-dd hh: mm: ss

. , . yyyy-mm-dd hh:mm:ss - , IDE, , . datetime . . CF/java unix epoch. , IDE , yyyy-mm-dd hh:mm:ss, .

. , , , , , .

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