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
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

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

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

Best regards
Henrik Staun Poulsen



mandu
mandu
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1541 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
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1541 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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15922 Visits: 11355
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
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 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
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15922 Visits: 11355
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