Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Comparison of Dates in SQL Expand / Collapse
Author
Message
Posted Monday, April 27, 2009 11:04 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 5, 2010 10:47 PM
Points: 93, Visits: 98
Comments posted to this topic are about the item Comparison of Dates in SQL
Post #705508
Posted Tuesday, April 28, 2009 1:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:47 AM
Points: 1,373, Visits: 993
Is SQL Server able to use indexes when using DataDiff(Day, D1, D2) = 0 ?

Best regards
Henrik Staun Poulsen



Post #705544
Posted Tuesday, April 28, 2009 1:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 2:22 AM
Points: 5, 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
Post #705545
Posted Tuesday, April 28, 2009 2:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:45 PM
Points: 1,385, Visits: 1,245
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.
Post #705568
Posted Tuesday, April 28, 2009 2:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 12:28 AM
Points: 2, 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

Post #705573
Posted Tuesday, April 28, 2009 2:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:45 PM
Points: 1,385, Visits: 1,245
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 :)



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.
Post #705581
Posted Tuesday, April 28, 2009 2:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 11,194, Visits: 11,166
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #705582
Posted Tuesday, April 28, 2009 2:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 12:28 AM
Points: 2, 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.
Post #705585
Posted Tuesday, April 28, 2009 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:41 PM
Points: 7, Visits: 228
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.
Post #705604
Posted Tuesday, April 28, 2009 3:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:59 AM
Points: 11,194, Visits: 11,166
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #705609
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse