Comparing Dates

Question

Comparing Dates

BSavoie

SSCertifiable

Points: 6309
More actions
December 24, 2009 at 3:35 pm

#228758

What is the optimal method to filter dates in a where clause if you want to ignore the time portion?
This works fine:
WHERE DateAdd(day, SomeDateColumn, SomeTestDate) > 0
but doesn't that screw up the optimizer? If there is an index on SomeDateColumn, it might not get used here right?
Then there's this old trick:
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, SomeDateColumn)) > SomeTestDate
which effetively truncates the time portion off the date column before doing the comparison, but this doesn't seem to be a performance home run either.
I've fallen in that old trap enough times where some rogue application accidentally includes a time when saving a date and breaks a query somewhere. I NEVER do direct date comparisons anymore, but it sure does cost me a lot of performance. MUST be a better way!
Thanks!
.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 1

BSavoie (12/24/2009)
What is the optimal method to filter dates in a where clause if you want to ignore the time portion?
This works fine:
WHERE DateAdd(day, SomeDateColumn, SomeTestDate) > 0
but doesn't that screw up the optimizer? If there is an index on SomeDateColumn, it might not get used here right?
Then there's this old trick:
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, SomeDateColumn)) > SomeTestDate
which effetively truncates the time portion off the date column before doing the comparison, but this doesn't seem to be a performance home run either.
I've fallen in that old trap enough times where some rogue application accidentally includes a time when saving a date and breaks a query somewhere. I NEVER do direct date comparisons anymore, but it sure does cost me a lot of performance. MUST be a better way!
Thanks!

Both of those methods "screw up the optimizer". If you want an index to be effectively used for something like this, then you need to use the following general format...

WHERE SomeDateColumn >= SomeTestDate -- SomeTestDate being a "whole" date here

AND SomeDateColumn < DATEADD(dd,1,SomeTestDate)

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

freeman.e.l SSC Veteran Points: 249 More actions · Answer 2

freeman.e.l

SSC Veteran

Points: 249

December 29, 2009 at 2:02 pm

#1097218

I Agree

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736212

December 29, 2009 at 2:13 pm

#1097224

Third vote here.

BSavoie SSCertifiable Points: 6309 More actions · Answer 4

When building indexes for dates, when I know I don't need the time portion, would it help to neutralize the time portion when building the index?

CREATE INDEX MyIndex ON ThisTable(

DATEADD(dd, 0, DATEDIFF(dd, 0, ThisCol))

)

.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

BSavoie (12/29/2009)
When building indexes for dates, when I know I don't need the time portion, would it help to neutralize the time portion when building the index?
CREATE INDEX MyIndex ON ThisTable(
DATEADD(dd, 0, DATEDIFF(dd, 0, ThisCol))
)

I wouldn't think this would work - but, since you posted in the 2008 forum I would suggest that you use the new DATE data type instead. You could easily create a computed column from your datetime column and then index that column. Then, when searching you wouldn't have to worry about the time portion at all.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

Just make sure that nothing will break if you shift to the new DATE datatype... rumor has it that you can no longer use somedatecolumn+1 to add a day for example. I believe you must convert such code to DATEADD(dd,1,somedatecolumn) or BOOM!

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

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 7

p.s. If a column has the times on it as well as the dates, that could be important information that will be lost if you strip the times for mere "programming convenience". Take it from one who has had to provide "testimonial data" to both the SEC and the FBI on more than one occasion. Not to sound too gruff but it's your hiney if you can't provide the data that should have been there and don't think that it'll never happen to you or your data is too "simple" for them to care about. Remember... it's YOUR hiney you're gambling with.

If you want to make your life a little easier, put a calculated column on the table and index it to give you "date only", but never change original data... it just isn't worth it. And screw the boss... it's not his/her hiney.

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

freeman.e.l SSC Veteran Points: 249 More actions · Answer 8

Most notable quote: "And screw the boss... it's not his/her hiney"

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

I have to say - I didn't even think I had suggested changing the actual column to a date data type. If it came across that way - I'm sorry. My intent was only to show that you could create a computed column using just the date data type and index that column.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

BSavoie SSCertifiable Points: 6309 More actions · Answer 10

Jeffrey, that's exactly what I understood you to say. Seems like a pretty clever idea. Probably not a good solution for EVERY date column in EVERY table. But it could be just the ticket for the couple we have in our system that give me trouble time after time. Certainly worth an experiment or two.

I agree with Jeff that it would be a very bad idea to just haphazardly truncate the time off of all the dates. This computed column could be just the thing, and I don't see any risk other than a pretty minor tax on resources.

Thanks All!

.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 11

Jeffrey Williams-493691 (12/29/2009)
I have to say - I didn't even think I had suggested changing the actual column to a date data type. If it came across that way - I'm sorry. My intent was only to show that you could create a computed column using just the date data type and index that column.

Sorry, Jeffrey. I took it the wrong way and was just making sure.

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

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 12

BSavoie (12/29/2009)
Jeffrey, that's exactly what I understood you to say. Seems like a pretty clever idea. Probably not a good solution for EVERY date column in EVERY table. But it could be just the ticket for the couple we have in our system that give me trouble time after time. Certainly worth an experiment or two.
I agree with Jeff that it would be a very bad idea to just haphazardly truncate the time off of all the dates. This computed column could be just the thing, and I don't see any risk other than a pretty minor tax on resources.
Thanks All!

It's a really minor tax, performance wise, if the computed column is PERSISTED although that does take a bit of extra space.

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

BSavoie SSCertifiable Points: 6309 More actions · Answer 13

Sorry, I'm back to this again!

Lets say I have a table with "ArrivalDateTime" column.

And I have a stored procedure that will fetch these. Something like this:

create procedure FindArrivals

@FromDate datetime

@ToDate datetime

BEGIN

SELECT * FROM ArrivalsTAble WHERE ArrivalDateTime BETWEEN @FromDate AND @ToDate

END

So the callers of this sproc are usually not interested in the TIME, they just want all the Arrivals between the two dates. Is the sproc correct for this scenario, AND will the SELECT be able to use the index that exists on ArrivalDateTime?

Thanks!

.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 14

BSavoie (1/8/2010)
Sorry, I'm back to this again!
Lets say I have a table with "ArrivalDateTime" column.
And I have a stored procedure that will fetch these. Something like this:
create procedure FindArrivals
@FromDate datetime
@ToDate datetime
BEGIN
SELECT * FROM ArrivalsTAble WHERE ArrivalDateTime BETWEEN @FromDate AND @ToDate
END
So the callers of this sproc are usually not interested in the TIME, they just want all the Arrivals between the two dates. Is the sproc correct for this scenario, AND will the SELECT be able to use the index that exists on ArrivalDateTime?
Thanks!

Not necessarily - if you pass in the following:

EXECUTE FindArrivals '20091201', '20091231';

Do you expect to see all rows where the ArrivalDateTime is '2009-12-31' and some time? If you have an ArrivalDateTime value of '2009-12-31 12:00:00.000' it will not be included because that value is greater than '2009-12-31' which will be implicitly converted to '2009-12-31 00:00:00.000'.

The better way to code this is to use a range check:

SELECT {columns}

FROM dbo.ArrivalsTable

WHERE ArrivalDateTime >= @FromDate

AND ArrivalDateTime < DATEADD(day, 1, @ToDate)

Now, be careful with how you pass in your dates. If you pass in a date with a time - then using either BETWEEN or a range check will take the time into consideration. To avoid that, remove the time portion from the input parameters like so:

SET @FromDate = DATEADD(day, DATEDIFF(day, 0, @FromDate), 0);

SET @ToDate = DATEADD(day, DATEDIFF(day, 0, @ToDate), 0);

This will insure that you include all values in the range.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs