SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Comparison of Dates in SQL


Comparison of Dates in SQL

Author
Message
Manish Sinha
Manish Sinha
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 98
Comments posted to this topic are about the item Comparison of Dates in SQL
henrik staun poulsen
henrik staun poulsen
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3308 Visits: 1247
Is SQL Server able to use indexes when using DataDiff(Day, D1, D2) = 0 ?

Best regards
Henrik Staun Poulsen



mandu
mandu
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 17
I think that this articole is very academic and it is not useful for work, because it works, it's true, but the indexes? Do the indexes works?

Bye
mandu
Tao Klerks
Tao Klerks
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 1249
Hi,

The following statement is untrue: "it is always a good practice to use DATEDIFF method for date comparison instead of making comparison using logical operators (<,>,=)".

As the previous two posters noted, using a function call on an indexed date column (or any indexed column, for that matter) prevents SQL Server from doing an efficient "seek" on the values in that column for that index - all the values need to be retrieved first, then the function applied. If the date column has the highest selectivity, then this can be a severe performance killer.

A safer way to ensure that you get all dates in a certain range, ignoring the "time" component, without applying a function to the source column, is using DateAdd as follows:

--select the correct data type here, DateTime vs SmallDateTime, 
-- as you might otherwise incurr another conversion function call
-- on the source column!
DECLARE @FromDate DateTime
DECLARE @ToAndIncludingDate DateTime

SET @FromDate = '2009-04-01'
SET @ToAndIncludingDate = '2009-04-15'

SELECT *
FROM SomeTable
WHERE (SomeColumn >= @FromDate AND SomeColumn < DateAdd(Day, 1, @ToAndIncludingDate) )



HTH,
Tao

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
benr-758674
benr-758674
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 66
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.

I have got a function that calculates the beginning of the day (effectively stripping any time information off the date). So in my report sprocs I do something like this:


CREATE PROC somereport
@startdate datetime
@enddate datetime
BEGIN
-- Fix up the dates (remove time portion from start date, get 12AM the day after the end date)
select @startdate = dbo.dbdate(@startdate), @enddate = dbo.dbdatenext(@enddate)

-- Do the query...
select *
from mysource
where somedate >= @startdate and somedate < @enddate -- Note I am selecting all records PRIOR to the next day
END



Since the @enddate is effectively the beginning of the day AFTER the date required, saying < will then get all records up to and including the end of the day requested...

Here are the date functions I use. Unfortunately I cannot remember if I wrote them myself or found them somewhere... My dbdate function used to use the convert function to get a string representation and then truncate and convert the result back into a date, but I ran into trouble with different regional settings (where dates and times are expressed differently). The method below is a bit slower and but seems to work for any regional setting.


-- Remove the time portion from a date.
CREATE FUNCTION [dbo].[DBDate] (@DATE datetime)
RETURNS datetime
AS
BEGIN
DECLARE @Date1 DateTime
SET @Date1 = @Date
SET @Date1 = DateAdd(ms, -1 * DatePart(ms, @Date1), @Date1)
SET @Date1 = DateAdd(ss, -1 * DatePart(ss, @Date1), @Date1)
SET @Date1 = DateAdd(mi, -1 * DatePart(mi, @Date1), @Date1)
SET @Date1 = DateAdd(hh, -1 * DatePart(hh, @Date1), @Date1)
RETURN @Date1
END

-- Get 12AM of the next day
CREATE FUNCTION [dbo].[DBDateNext] (@DATE datetime)
RETURNS datetime
AS
BEGIN
RETURN DateAdd(dd, 1, dbo.DBDate(@Date))
END


Tao Klerks
Tao Klerks
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 1249
Interesting approach - we use Convert, relying on the fact that DateTime values are internally stored as floating-point values, with every whole number representing a day:

SET @DateWithoutTime = Convert(DateTime, Floor(Convert(Float, @OriginalDate)))

Any idea which is faster? I don't have time to test right now, but I'm quite curious Smile

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Paul White
Paul White
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61628 Visits: 11397
henrik staun poulsen (4/28/2009)
Is SQL Server able to use indexes when using DateDiff(Day, D1, D2) = 0 ?
Best regards
Henrik Staun Poulsen

FTFY :-)

The answer is yes.

But only if:


  • There is an index on a persisted column in the table that is defined as datediff(day,d1,d2); or

  • There is an index on an indexed view column which resolves to datediff(day,d1,d2)



Otherwise, no ;-)

Cheers,

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
benr-758674
benr-758674
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 66
Hi Tao - guess we could always just run both methods over a largish table and get stats out of the query analyser (like cpu usage, etc) ... In terms of finding the start of a day - the method I proposed has a lot of steps in it than yours - so its probably slower, also, your method is compact enough just to use in the original sproc rather than doing a function call.
craig.lovegren
craig.lovegren
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 244
guess we could always just run both methods over a largish table and get stats out of the query analyser (like cpu usage, etc)


I did exactly that, using the small example, you find that the datediff and dateadd are nearly identical, indexes or not. So I grabbed a large table (a few million rows) from our systems, indexed them appropriately and ran a few tests. When comparing my three tests, datediff ranked at 90%, dateadd, although a complex plan, took only 9%, and the final test took the remaining 1%. What we typically do for tables which are queried on a datetime field religiously is to actually create another column with the date portion only. It does require an extra 4 bytes (smalldatetime) per row, but disk space is cheap. Just remember to update the column after the data load in a batch update statement, don't use triggers, computed columns, or calcs in your inserts, as this will slow your data loads down drastically.
Paul White
Paul White
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61628 Visits: 11397
Tao Klerks (4/28/2009)
The following statement is untrue: "it is always a good practice to use DATEDIFF method for date comparison instead of making comparison using logical operators (<,>,=)".

Agreed.

Tao Klerks (4/28/2009)

As the previous two posters noted, using a function call on an indexed date column (or any indexed column, for that matter) prevents SQL Server from doing an efficient "seek" on the values in that column for that index - all the values need to be retrieved first, then the function applied. If the date column has the highest selectivity, then this can be a severe performance killer.

Not always.

As mentioned previously, the QO will consider using a useful index containing a function on a persisted column or as part of any indexed view (enterprise only). Note that the QO may consider using an index from a view if that index covers a guaranteed superset of the target rows. See http://msdn.microsoft.com/en-us/library/ms187864(SQL.90).aspx or Books Online for details.

There are some trivial examples where a function will not prevent a seek - one is adding ISNULL around a non-nullable column. This doesn't work with COALESCE.

Tao Klerks (4/28/2009)

A safer way to ensure that you get all dates in a certain range, ignoring the "time" component, without applying a function to the source column, is using DateAdd...

True. Though the example given may not result in a good plan either, because SQL Server cannot sniff the local variable parameters. The usual solutions apply: create a nested procedure, use dynamic SQL, add OPTION (RECOMPILE) or (OPTIMIZE FOR(@var=value)).

Cheers,

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search