Comparison of Dates in SQL

Question

Comparison of Dates in SQL

Viewing 15 posts - 61 through 75 (of 110 total)

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

gideon.kahl SSC-Addicted Points: 489 More actions · Answer 1

If the manager says, "I want all the employees who joined in year 2009 until 06-Feb-2009." In SQL, this is a very simple query - Date of Joining >= (greater than equal to) 01-01-2009 and = '20090101' and [Date of Joining] < '20090207'. Indexes will still be used and there is no overhead with regards to functions that need to be performed on each row.

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

craig.lovegren (4/28/2009)
Jeff Moden (4/28/2009)
Percent of what? Percent of BATCH??? That absolutely cannot be trusted. Only actual cpu usage, duration, reads, and writes should be used to measure performance.
[p]Actually, it was an overall grade.[/p]Pseudo-code:
build a table containing test #, iteration #, and precomputed random date inside valid range, no time values
define a local, static, forward only, read only cursor to extract the dates one by one (I know, cursors, ick)
dbcc dropcleanbuffers
dbcc freeproccache
set statistics io on
set statistics time on
execute test:
#1: DATEDIFF( d, ((column)), ((testdate)) ) = 0
#2: ((column)) >= ((testdate)) AND ((column)) < DATEADD( d, 1, ((testdate)) )
#3: ((stored truncated date column)) = ((testdate))
set statistics io off
set statistics time off
loop to next iteration
The results were taken from the Output window, run through Perl, and shoved in to a table.
Oddly, the results I am getting now are different than this morning; I can only assume I didn't rebuild the indexes the same way I did this morning. Test #2 and #3 are showing almost identical results... hmmm.
Test 1: 5889 cpu, 17 scans, 78,030 logical reads
Test 2: 27 cpu, 1 scan, 696 logical reads
Test 3: 23 cpu, 1 scan, 672 logical reads

Heh... tests for performance just don't count if you're using a cursor. Any chance of you posting your actual code?

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

craig.lovegren (4/28/2009)
rob.lobbe (4/28/2009)
Not sure why dates cause so much trouble...
declare
@date datetime,
@today datetime,
@tonight datetime
select
@date = getdate(),
@today = convert(datetime,convert(int,@date)),--use smallint for smalldatetime
@tonight = dateadd(ms,-3,@today+1) -- 0.003 is the smallest unit for datetime
select
@date,
@today,
@tonight
Did you check your output?
@date = '2009-04-28 16:06:36.970',
@today = '2009-04-29', -- oops, it's not tomorrow quite yet
@tonight = '2009-04-29 23:59:59.997' -- again, in the futureAfter noon, casting to an INT will round to the following day.

]

Again this code will break with new, more precise, datetime datatypes in SQL Server 2008.

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

rob.lobbe-964963 Old Hand Points: 307 More actions · Answer 4

TheSQLGuru (4/28/2009)

Again this code will break with new, more precise, datetime datatypes in SQL Server 2008.

Well if you change the code - ie the data type - of course you need to make adjustments.

For a datatype of datetime it still works in 2008.

The whole point was to initially establish an @today - and by extention an @tonight (get them however you like)

With this as a reference all other datetime calculations can use these as a 'base' for most reporting requirements.

Aneesh-469647 SSC Veteran Points: 211 More actions · Answer 5

Hello,

Using SQL function in where condition is not a good practice.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Aneesh (4/28/2009)
Hello,
Using SQL function in where condition is not a good practice.

That is dependent on how and where you use the SQL functions in the WHERE clause.

Paul White SSC Guru Points: 150467 More actions · Answer 7

Aneesh (4/28/2009)
Using SQL function in where condition is not a good practice.

So one should only use functions in SELECT statements?

Is that your point?

😛

To be fair, I think your comment was well motivated, but you should have qualified it.

There are good ways and bad ways to use the various kinds of UDFs, both T-SQL and CLR.

Making sweeping statements about complex subjects is not a good practice :laugh:

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

cavellbd Grasshopper Points: 15 More actions · Answer 8

i might be wrong, but cant we easily use a query like this?

SELECT * FROM EmployeeHours WHERE CONVERT(VARCHAR(10), Date, 121) = '2009-06-02'

Paul White SSC Guru Points: 150467 More actions · Answer 9

cavellbd (4/29/2009)
i might be wrong, but cant we easily use a query like this?
SELECT * FROM EmployeeHours WHERE CONVERT(VARCHAR(10), Date, 121) = '2009-06-02'

You are not wrong, but as been mentioned a few times now (with similar suggestions), the downside is that the conversion means the query optimizer has to scan any useful index on Date, rather than seeking to the specified value.

The scan is usually much less efficient than the seek, depending on how many rows would be selected.

See earlier posts for a deeper explanation.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dallas Martin SSC Veteran Points: 231 More actions · Answer 10

A trigger in this instance would hardly bring the system to its knees.

A trigger doesn't participate in a SELECT.

The entire article concerns the handling date queries.

I suggested an approach that would avoid data conversion during a SELECT. A SELECT would be slowed using a WHERE clause with a conversion function. In some cases, it could drastically cause a huge

performance hit.

Paul White SSC Guru Points: 150467 More actions · Answer 11

(original post is here)

Dallas Martin (4/29/2009)
A trigger in this instance would hardly bring the system to its knees.

Sure, I was just pointing out that it isn't a good solution. I don't think I said it would down the system.

Dallas Martin (4/29/2009)
A trigger doesn't participate in a SELECT.

True. Not quite sure why you bring that up, but nevertheless.

Dallas Martin (4/29/2009)
The entire article concerns the handling date queries.

Also true. You seem to be on a roll here.

Dallas Martin (4/29/2009)
I suggested an approach that would avoid data conversion during a SELECT. A SELECT would be slowed using a WHERE clause with a conversion function. In some cases, it could drastically cause a huge performance hit.

Ah. Not so good.

I think if you review the other posts to this thread, you will see why indexing a CHAR(8) in an unbound column is not optimal.

It would work, sure. But that's not the point - there are much better solutions.

I would direct you in particular to a post by Lynn Pettis:

Lynn
...where
referencedate >= dateadd(dd, datediff(dd, 0, @comparedate), 0) and -- Beginning of this day
referencedate < dateadd(dd, datediff(dd, 0, @comparedate) + 1, 0) -- Beginning of next day
This will allow the QO to use the index on referencedate, if it exists.

Armed with that, and other good stuff from Jeff, Gail, et al., I feel confident that we can avoid any drastically huge performance hits, without having to add any columns or indexes.

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

itamar-863608 SSC Veteran Points: 285 More actions · Answer 12

How about different kind of SQL compare tools, they work OK with dates?

--------

SQL Data compare , Synchronize data from diffrent surces, and many other SQL tools[/url].

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 13

rob.lobbe (4/28/2009)

TheSQLGuru (4/28/2009)
Again this code will break with new, more precise, datetime datatypes in SQL Server 2008.
Well if you change the code - ie the data type - of course you need to make adjustments.
For a datatype of datetime it still works in 2008.
The whole point was to initially establish an @today - and by extention an @tonight (get them however you like)
With this as a reference all other datetime calculations can use these as a 'base' for most reporting requirements.

YOU may know that this will break with a datatype that is precise down to nanoseconds, but other readers who do a search and find this post may well not know that and will have bad code.

It is much better, IMHO, to simply use the start of the next day, i.e. 2/13/2009 00:00:00 and use a < comparison to ensure you get all of the prior day's data but nothing beyond that. This works for every datatype that includes the time.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 14

itamar (4/29/2009)
How about different kind of SQL compare tools, they work OK with dates?

when using comparison tools one is most often comparing the entire table anyway, so index scan/seek issues are moot.

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 15

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.