Comparison of Dates in SQL

Question

Comparison of Dates in SQL

Viewing 15 posts - 76 through 90 (of 110 total)

You must be logged in to reply to this topic. Login to reply

Soki SSC Rookie Points: 31 More actions · Answer 1

benr (4/28/2009)
I am pretty sure that use of datediff won't work well with indexes, since it requires a calculation to be performed on every row that is being compared.
When I am working with dates, I always say >= the begin date, and then < the next day. Eg. If you want all records up to and including the end of 31st March 2009 your query should ask for all records prior to the 1st of April. This method should then be able to leverage indexes.

Actually yes, when dealing with comparison against fixed values (ie: variables) it's a TERRIBLE idea to use functions on the columns, functions create a lot of overhead and block the indexes from being used properly, (I'm actually guessing you might even loose SARGability), the best way to do this is to make the vars hold values that will remove the function from the query, one way is to use the < value and add a date (assuming you're leaving the hour at 00:00:00 otherwise you'll screw your filter) or even to calculate to the date you want and time as 23:59:59 since you only do this once, the overhead performance is almost zero.

Another good way to handle dates in case you're interested in performance (for example on DWH) is to use Julian dates, this is the fastest way to retrieve datas from huge tables.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Nope... 23:59:59 misses almost a whole second of the day. The >= and < method is probably the best method there is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Amber.Brouillard (4/29/2009)
I discovered this several months back, but I use: dateadd(day,1,) as my fix. Just another way of doing it.
This is VERY useful information! Thanks for posting it.

If the date has a time with it, that could be a problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 4

Soki (4/29/2009)
Actually yes, when dealing with .... blah blah blah

Soki,

I don't have time for a comprehensive reply, so I would just invite you to read the other posts on this thread - especially those from Jeff Moden and Lynn Pettis. You should be able to find a few problems with the reply you posted 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ashish Pathak SSC Rookie Points: 26 More actions · Answer 5

Posted comment on Comparision of Dates in SQL looks fine. But as written above, it may not be the best solution in case of using indexes. Well... while comparing two datetime I prefer to use below:

SELECT

Count(EmpID)

FROM

Employee

WHERE

Convert(Varchar(8), EnteredDate, 112) >= Convert(Varchar(8), Date1, 112)

Convert(Varchar(8), EnteredDate, 112) <= Convert(Varchar(8), Date2, 112)

Above will give the desired output.

Regards,

Ashish

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Ashish Pathak (4/30/2009)
Posted comment on Comparision of Dates in SQL looks fine. But as written above, it may not be the best solution in case of using indexes. Well... while comparing two datetime I prefer to use below:
SELECT
Count(EmpID)
FROM
Employee
WHERE
Convert(Varchar(8), EnteredDate, 112) >= Convert(Varchar(8), Date1, 112)
Convert(Varchar(8), EnteredDate, 112) <= Convert(Varchar(8), Date2, 112)
Above will give the desired output.
Regards,
Ashish

Once again a post that voids the use of index seeks to resolve the query. Ashish, please review this entire thread to determine why you present a suboptimal solution.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Amber.Brouillard Valued Member Points: 50 More actions · Answer 7

Hi Jeff,

The dates I use do have a time with them (that is how SQL stores them) and dateadd(day, 1, date) still works fine.

Thanks!

Amber

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 8

Amber.Brouillard (4/30/2009)
Hi Jeff,
The dates I use do have a time with them (that is how SQL stores them) and dateadd(day, 1, date) still works fine.
Thanks!
Amber

If your dates have times with them (which all DATETIME data does in SQL Server) then your example is NOT accurate. It will not work properly.

Take a look at this code.

The goal is to retrieve all data up until a given day.

If we do it your way it does not work properly.

DECLARE @CurrentDate DATETIME

SET @CurrentDate = GETUTCDATE()

DECLARE @Sample TABLE

(

SomeID INT IDENTITY(1,1)

,SomeDate DATETIME

)

INSERT INTO @Sample

SELECT '2009-04-30 18:29'

UNION ALL SELECT '2009-04-30 10:29'

UNION ALL SELECT '2009-04-30 19:29'

UNION ALL SELECT '2009-04-30 20:29'

UNION ALL SELECT '2009-04-30 00:00'

UNION ALL SELECT '2009-04-30 18:30'

UNION ALL SELECT '2009-04-29 10:29'

UNION ALL SELECT '2009-04-29 11:05'

UNION ALL SELECT '2009-04-29 05:13'

SELECT *

FROM @Sample

WHERE SomeDate = @CurrentDate

Amber.Brouillard Valued Member Points: 50 More actions · Answer 9

I'm not going to argue with you about this. I've been using dateadd(day, 1, date) for about six months and I use it in many different programs and stored procedures. It has worked fine for me without any issues. I do not know why it doesn't work for you, but it has consistently worked for me (and continues to work).

Blessings!

Amber

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 10

Amber.Brouillard (4/30/2009)
I'm not going to argue with you about this. I've been using dateadd(day, 1, date) for about six months and I use it in many different programs and stored procedures. It has worked fine for me without any issues. I do not know why it doesn't work for you, but it has consistently worked for me (and continues to work).
Blessings!
Amber

Nobody is trying to argue with you.

Just trying to show you, that from many years experience, it simply does not work.

I suggest you take a closer look at your code, it may fail in some cases and it's better to catch it now then later. 🙂

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 11

Goldie Graber (4/30/2009)
Amber.Brouillard (4/30/2009)
Hi Jeff,
The dates I use do have a time with them (that is how SQL stores them) and dateadd(day, 1, date) still works fine.
Thanks!
Amber
If your dates have times with them (which all DATETIME data does in SQL Server) then your example is NOT accurate. It will not work properly.
Take a look at this code.
The goal is to retrieve all data up until a given day.
If we do it your way it does not work properly.
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETUTCDATE()
DECLARE @Sample TABLE
(
SomeID INT IDENTITY(1,1)
,SomeDate DATETIME
)
INSERT INTO @Sample
SELECT '2009-04-30 18:29'
UNION ALL SELECT '2009-04-30 10:29'
UNION ALL SELECT '2009-04-30 19:29'
UNION ALL SELECT '2009-04-30 20:29'
UNION ALL SELECT '2009-04-30 00:00'
UNION ALL SELECT '2009-04-30 18:30'
UNION ALL SELECT '2009-04-29 10:29'
UNION ALL SELECT '2009-04-29 11:05'
UNION ALL SELECT '2009-04-29 05:13'
SELECT *
FROM @Sample
WHERE SomeDate = @CurrentDate

Amber, there is no argument here. This thread is about getting all records through a given day. Your method simply fails to accomplish that given certain time values as part of the date field. Sorry, but that is not debatable - it is a fact. IF you ALWAYS get the right answer with your method then a) your objective is not the same as this thread's, b) you don't have time values such as exhibited here or c) you really are getting incorrect data but aren't aware of it.

There is also the same issue of index scan/seek if you are using a function on a column in a where clause.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Amber.Brouillard Valued Member Points: 50 More actions · Answer 12

DECLARE @CurrentDate DATETIME

SET @CurrentDate = GETDATE()

DECLARE @Sample TABLE

(

SomeID INT IDENTITY(1,1)

,SomeDate DATETIME

)

INSERT INTO @Sample

SELECT '2009-04-30 18:29'

UNION ALL SELECT '2009-04-30 10:29'

UNION ALL SELECT '2009-04-30 19:29'

UNION ALL SELECT '2009-04-30 20:29'

UNION ALL SELECT '2009-04-30 00:00'

UNION ALL SELECT '2009-04-30 18:30'

UNION ALL SELECT '2009-04-29 10:29'

UNION ALL SELECT '2009-04-29 11:05'

UNION ALL SELECT '2009-04-29 05:13'

----added dates

UNION ALL SELECT '2009-05-01 18:30'

UNION ALL SELECT '2009-05-01 10:29'

UNION ALL SELECT '2009-05-01 11:05'

UNION ALL SELECT '2009-05-01 05:13'

SELECT *

FROM @Sample

WHERE SomeDate BETWEEN @CurrentDate AND DATEADD(DAY, 1, @CurrentDate)

With this, I get both today's date (4/30/09) AND all of the records with tomorrow's date.

Blessings!

Amber

Amber.Brouillard Valued Member Points: 50 More actions · Answer 13

Nevermind, as I look at it closer, I see your point. Thanks for the correction.

Blessings!

Amber

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 14

TheSQLGuru (4/30/2009)
Goldie Graber (4/30/2009)
Amber.Brouillard (4/30/2009)
Hi Jeff,
The dates I use do have a time with them (that is how SQL stores them) and dateadd(day, 1, date) still works fine.
Thanks!
Amber
If your dates have times with them (which all DATETIME data does in SQL Server) then your example is NOT accurate. It will not work properly.
Take a look at this code.
The goal is to retrieve all data up until a given day.
If we do it your way it does not work properly.
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETUTCDATE()
DECLARE @Sample TABLE
(
SomeID INT IDENTITY(1,1)
,SomeDate DATETIME
)
INSERT INTO @Sample
SELECT '2009-04-30 18:29'
UNION ALL SELECT '2009-04-30 10:29'
UNION ALL SELECT '2009-04-30 19:29'
UNION ALL SELECT '2009-04-30 20:29'
UNION ALL SELECT '2009-04-30 00:00'
UNION ALL SELECT '2009-04-30 18:30'
UNION ALL SELECT '2009-04-29 10:29'
UNION ALL SELECT '2009-04-29 11:05'
UNION ALL SELECT '2009-04-29 05:13'
SELECT *
FROM @Sample
WHERE SomeDate = @CurrentDate
Amber, there is no argument here. This thread is about getting all records through a given day. Your method simply fails to accomplish that given certain time values as part of the date field. Sorry, but that is not debatable - it is a fact. IF you ALWAYS get the right answer with your method then a) your objective is not the same as this thread's, b) you don't have time values such as exhibited here or c) you really are getting incorrect data but aren't aware of it.
There is also the same issue of index scan/seek if you are using a function on a column in a where clause.

Let's take a closer look.

Run this query to see how many records there are per day

SELECT *

FROM @Sample

ORDER BY SomeDate

There are 13 records in the file and the breakdown is as follows:

04/29/2009 - 3 records

04/30/2009 - 6 records

05/01/2009 - 4 records

Accordingly, if we want data from 4/29 and 5/1, 10 records should be returned.

However your query only returns 7 records and the breakdown is as follows

04/30/2009 - 4 records

05/01/2009 - 3 records

You are missing 3 records 😉

Amber.Brouillard Valued Member Points: 50 More actions · Answer 15

Amber.Brouillard

Valued Member

Points: 50

April 30, 2009 at 1:50 pm

#986921

Yeah, I saw that after I posted my response. Thanks!