How to compare dates in SQL Server

Question

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?

There is no such built in method like #Dateformat. You can use convert.search for it — polin, Dec 02 '12 at 06:11
Why don't you try and change the format while forming the query — Shamis Shukoor, Dec 02 '12 at 06:18
@polin huh? DateFormat exists! http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7ff4.html — Russ, Dec 02 '12 at 06:28
@Russ obviously DateFormat exists but not #DateFormat as used in query. — polin, Dec 02 '12 at 06:30
@Polin In Coldfusion, the # signs are used for inserting dynamic values. This particular piece of code must exist within a cfquery block, in which you can insert dynamic values as the OP is attempting. — Russ, Dec 02 '12 at 06:38
When I work with DBAs, the # makes them look twice. They think I am doing something with a temp table — James A Mohler, Dec 02 '12 at 06:52
What datatype is the `event_date` column. If it is `DateTime` then **it has no format**. The formatting is done by your client application (or SQL tool) upon displaying the values. — , Dec 02 '12 at 07:32
DateFormat returns a string. Avoid it. Use parsedatetime to create datetime variables instead. — Dan Bracuk, Dec 02 '12 at 15:28
Is event date expected to have time, or does all activity happen at midnight? This is important to know in order to make sure that all event_dates are picked up — James A Mohler, Dec 02 '12 at 19:18

James A Mohler · Accepted Answer · 2012-12-02T10:38:55.523

3

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>

edited Dec 02 '12 at 10:38

answered Dec 02 '12 at 06:32

James A Mohler

11,060
15
46
72

1

Should probably validate that the form data *are dates* before slinging them at the DB, and not just relying on the `` to error if they're not. – Adam Cameron Dec 02 '12 at 08:20
Added. The Select * should also go. And dbo.user_action_events should be used. and maybe a WITH (NOLOCK) – James A Mohler Dec 02 '12 at 10:32
2

Why `CONVERT(DATE, event_date)`? This will fobid the use of indexes. – ypercubeᵀᴹ Dec 02 '12 at 10:36
OK, I think I am including the entire end day – James A Mohler Dec 02 '12 at 10:41
2

Instead of using between, I would use >= theStartDate and < theDayAfterTheEndDate. Using between might return too many records. – Dan Bracuk Dec 02 '12 at 15:27
1

If you want to do a between search on datetime fields you have to make sure the end variable has 23 hours, 59 minutes, and 59 seconds added to it - possibly even more, depending on how precise the db is storing those values. – Dan Bracuk Dec 02 '12 at 20:32
Yeah, technically that still grabs a little too much because `between` is inclusive in ms sql. Using `< theDayAfterTheEndDateAtMidngiht`, instead of between, is a simple way around that problem. – Leigh Dec 03 '12 at 21:07

Leigh · Answer 2 · 2012-12-03T16:41:59.743

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

How to compare dates in SQL Server

2 Answers2

Note about dates:

Linked