Comparing a Date column with a datetime value

Question

I have found the below query in one our stored procedures

SELECT *FROM TABLE1
WHERE (CONVERT(DATE,DateTo) BETWEEN @wkstdate AND @wkenddate))

Since Usage of Functions in where clause may hinder the performance I have changed it as below,

 SELECT *FROM TABLE1
 WHERE DateTo BETWEEN @wkstdate AND @wkenddate

The result is same after changing the codes. But i am not sure whether both will give same result in all the time. Any Scenarios where the above codes bring different results?

(P.S: @wkstdate and @wkenddate are DATE values & DateTo is a DATETIME value)

Appreciate Your Suggestions

you should use CONVERT(DATE,DateTo) BECAUSE MAKE sure in convert to DATE — Chanom First, Sep 21 '15 at 08:15
previously answered question see: http://stackoverflow.com/questions/25564482/how-to-compare-datetime-with-only-date-in-sql-server/25564544#25564544 — Paul Maxwell, Sep 21 '15 at 09:05
Do NOT use BETWEEN for date ranges, full stop (i.e. no exceptions). yes yes someone will point out you can cast to date and it works ok, but the general case you need to understand first. Best practice is to simply stop using between for date ranges. use a combination of >= with < (as shown at end of answer by Felix Pamittan, below) — Paul Maxwell, Sep 21 '15 at 09:09

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

This will not yield the same result.

Let's say your DateTo, which is a DATETIME value, has a time component:

'2015-09-21 01:00:00'

Your @wkenddate is '2015-09-21'. The WHERE DateTo BETWEEN @wkstdate AND @wkenddate will not retrieve the above row since '2015-09-21 01:00:00' > @wkenddate.

For more example:

CREATE TABLE tbl(DateTo DATETIME)
INSERT INTO tbl 
    SELECT CAST('2015-09-21 00:00:00.000' AS DATETIME) UNION ALL
    SELECT CAST('2015-09-21 16:10:49.047' AS DATETIME) UNION ALL
    SELECT CAST('2015-09-22 16:10:49.047' AS DATETIME) UNION ALL
    SELECT CAST('2015-09-20 16:10:49.047' AS DATETIME) 

DECLARE  @wkstdate DATE = '20150921',
         @wkenddate DATE = '20150921'
SELECT * 
FROM tbl
WHERE DateTo BETWEEN @wkstdate AND @wkenddate

SELECT * FROM tbl
WHERE (CONVERT(DATE,DateTo) BETWEEN @wkstdate AND @wkenddate)


DROP TABLE tbl

Now, using function in WHERE clause does make your query un-SARGable but there are exceptions. One of them is CASTing to DATE.

Another alternative if you do not want to CAST to DATE is to not use the BETWEEN operator. Instead use >= and <:

WHERE
    DateTo >= @wkstdate 
    AND DateTo < DATEADD(DAY, 1, @wkenddate)

score 0 · Answer 2 · answered Sep 21 '15 at 08:17

The BETWEEN operator will not cope properly with Times on your date data. So if you have two dates 1/1/2000 and 2/1/2000, and then ask for BETWEEN to work on a datetime like 2/1/2000 14:00, then this datetime does NOT fall between them. Stripping the Time portion off the datetime is advisable, using your CONVERT function as in your example is probably the best way. There are other ways to strip off the Time portion, but CONVERT is probably the most efficient. (My example using dd/mm/yyyy format)

What is the least efficient thing I noticed about your stored procedure is the use of SELECT * FROM. Try to use explicit field selections - to minimize the load on SQL if you want more efficient Stored procedures.

Anup Agrawal · Answer 3 · 2015-09-21T08:36:12.220

Yes both will give different results.

Lets say wkStDate = 9/1/2015 and wkEndDta = 9/30/2015

DateTo = 9/1/2015 18:00 HRS (will be included in both Cases)

DateTo = 9/21/2105 18:00 HRS (will be included in both cases)

DateTo = 9/30/2105 18:00 HRS (will be included in original query but excluded in second query without the convert)

In other words anyvalue where date part is same as end date and has time greater than midnight will be excluded from your query without the convert function i.e. WkEndDate + 00:00:01 To WkEndDate + 23:59:59 will be excluded. All other dates will show the same result.

If your DateTo is date time and will never has a time other than midnight then both the queries will give same result.

Comparing a Date column with a datetime value

3 Answers3