SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Manish Sinha / Comparison of Dates in SQL / Latest Posts

RE: Comparison of Dates in SQL

Lynn Pettis — Fri, 01 May 2009 15:47:08 GMT

I did a little more testing and wanted to post the results of the testing. These are in the same order as the queries in my previous post:[code]CpuMs LogRds Elapsed9344 5164 29506CpuMs LogRds Elapsed844 5164 29276CpuM LogRds Elapsed921 5164 29339[/code]

RE: Comparison of Dates in SQL

Lynn Pettis — Fri, 01 May 2009 13:38:18 GMT

Okay, simple test, both returned the same actual execution plan.Functions:[code]CREATE FUNCTION dbo.ufnDayOnly(@DateValue DateTime)RETURNS tableASRETURN (select dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnly )GOCREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)RETURNS DateTimeWITH SCHEMABINDINGASBEGIN RETURN dateadd(dd, datediff(dd,0, @DateValue),0) ENDGO[/code]Test code:[code]create table dbo.LAPTest ( AccountID int, Amount money, Date datetime);gocreate clustered index IX1_LAPTest on dbo.LAPTest ( AccountID asc, Date asc);go--===== Build the table 100 rows at a time to "mix things up"DECLARE @Counter INTSET @Counter = 0WHILE @Counter < 1000000BEGIN --===== Add 100 rows to the test table INSERT INTO dbo.LAPTest( AccountID, Amount, Date) SELECT TOP 100 AccountID =ABS(CHECKSUM(NEWID()))%50000+1, Amount =CAST(CHECKSUM(NEWID())%10000 /100.0 AS MONEY), Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME) FROM master.sys.columns t1 CROSS JOIN master.sys.columns t2 --===== Increment the counter SET @Counter = @Counter + 100END;goselect *, dbo.fn_DayOnly_DateTime (Date) from dbo.LAPTest;select * from dbo.LAPTest cross apply dbo.ufnDayOnly(Date);select *, dateadd(dd, datediff(dd,0, Date),0) as DateOnly from dbo.LAPTest;godrop table dbo.LAPTest;go[/code]Edit: Added a third query to the test code, but I haven't uploaded the third execution plan. Why, because it is identical to the other 2 I already uploaded.

RE: Comparison of Dates in SQL

GilaMonster — Fri, 01 May 2009 13:26:41 GMT

[quote][b]Tao Klerks (5/1/2009)[/b][hr][quote][b]GilaMonster (5/1/2009)[/b][hr]Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.Long answer - [url]http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/[/url][/quote]Do you have a ready blog article for every question ever raised? :-)[/quote]:-DI was wondering if you'd think that.Actually it's the other way around. I use questions here as ideas for blogs and I've seen a lot of problems with UDFs over time. Half the reason I write blog entries is so that I have something I can direct people to instead of having to write a couple pages every time common questions come up.

RE: Comparison of Dates in SQL

Paul White — Fri, 01 May 2009 13:23:32 GMT

Tao,No; it just seems that way because the top N articles cover the top 80% of commonly-asked questions like this one about UDFs.Paul

RE: Comparison of Dates in SQL

Tao Klerks — Fri, 01 May 2009 13:21:36 GMT

[quote][b]GilaMonster (5/1/2009)[/b][hr]Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.Long answer - [url]http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/[/url][/quote]Do you have a ready blog article for every question ever raised? :-)I think I'm going to go print it all out and peruse over the weekend.

RE: Comparison of Dates in SQL

Tao Klerks — Fri, 01 May 2009 13:19:07 GMT

[quote][b]Lynn Pettis (5/1/2009)[/b][hr]Just for S & G's, try this with a CROSS APPLY:[/quote]Sorry, I'm in an all-2000 environment right now. Testing is just a question of copying Gail's code from the link above and adding an extra case, however, if you're interested...

RE: Comparison of Dates in SQL

GilaMonster — Fri, 01 May 2009 12:22:11 GMT

[quote][b]Tao Klerks (5/1/2009)[/b][hr]Does that make sense? Can a UDF really be that expensive?[/quote]Yes. Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.Long answer - [url]http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/[/url]

RE: Comparison of Dates in SQL

Lynn Pettis — Fri, 01 May 2009 12:12:58 GMT

[quote][b]Tao Klerks (5/1/2009)[/b][hr]Alright, alright, point taken - no more converting to float... now I need to go explain the dateadd/datediff thing to my developers... I was hoping to be able to provide a nice UDF wrapper, but running Gail's test code from [url]http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/[/url], i find a 60X increase in CPU time when you add the UDF... :(Does that make sense? Can a UDF really be that expensive?(just in case I did something stupid, here is the function):[code]CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)RETURNS DateTimeWITH SCHEMABINDINGASBEGIN RETURN dateadd(dd, datediff(dd,0, @DateValue),0) ENDGO[/code][/quote]Just for S & G's, try this with a CROSS APPLY:[code]CREATE FUNCTION dbo.ufnDayOnly(@DateValue DateTime)RETURNS tableASRETURN dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnlyENDGO[/code]

RE: Comparison of Dates in SQL

Tao Klerks — Fri, 01 May 2009 11:42:36 GMT

Alright, alright, point taken - no more converting to float... now I need to go explain the dateadd/datediff thing to my developers... I was hoping to be able to provide a nice UDF wrapper, but running Gail's test code from [url]http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/[/url], i find a 60X increase in CPU time when you add the UDF... :(Does that make sense? Can a UDF really be that expensive?(just in case I did something stupid, here is the function):[code]CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)RETURNS DateTimeWITH SCHEMABINDINGASBEGIN RETURN dateadd(dd, datediff(dd,0, @DateValue),0) ENDGO[/code]

RE: Comparison of Dates in SQL

GilaMonster — Fri, 01 May 2009 10:51:44 GMT

No. Nor the version that casts to int.[code]DECLARE @Today2 DATETIME2 = GETDATE()SELECT CAST(FLOOR(CAST (@Today2 AS FLOAT)) AS DATETIME2)[/code][quote][color="#FF0000"]Msg 529, Level 16, State 2, Line 5Explicit conversion from data type datetime2 to float is not allowed.[/color][/quote]

RE: Comparison of Dates in SQL

Paul White — Fri, 01 May 2009 09:55:30 GMT

Does the hack with FLOATs from DATETIMEs still work with DATETIME2?

RE: Comparison of Dates in SQL

GilaMonster — Fri, 01 May 2009 09:41:21 GMT

[quote][b]Ed (5/1/2009)[/b][hr]Using -3 millliseconds will give you the maximum time Sql Server can use.[/quote]True with datetime, but no longer true on SQL 2008 if you start messing with the higher precision datetime data types. I've seen someone carry that 'convention' over to DATETIME2 and then wonder why they're missing rows.

RE: Comparison of Dates in SQL

Lynn Pettis — Fri, 01 May 2009 09:34:12 GMT

[quote][b]Ed (5/1/2009)[/b][hr]Using the DATEDIFF function works but can be very slow.I prefer using:DECLARE @FromDate DATETIME, @ThruDate DATETIME SET @FromDate = CONVERT(VARCHAR, GETDATE(), 101) --mm/dd/yyyy 00:00:00.000SET @ThruDate = DATEADD(DAY, 1, @FromDate) SET @ThruDate = DATEADD(MS, -3, @ThruDate) --mm/dd/yyyy 23:59:59.997SELECT * FROM Employee WHERE HireDate BETWEEN @FromDate AND @ThruDateUsing -3 millliseconds will give you the maximum time Sql Server can use.[/quote]I'd prefer this:DECLARE @FromDate DATETIME, @ThruDate DATETIME SET @FromDate = dateadd(dd, datediff(dd, 0, getdate()), 0) --mm/dd/yyyy 00:00:00.000SET @ThruDate = dateadd(dd, 1, @FromDate) SELECT * FROM Employee WHERE HireDate >= @FromDate AND HireDate < @ThruDate;

RE: Comparison of Dates in SQL

EdSwiedler — Fri, 01 May 2009 08:49:29 GMT

Using the DATEDIFF function works but can be very slow.I prefer using:DECLARE @FromDate DATETIME, @ThruDate DATETIME SET @FromDate = CONVERT(VARCHAR, GETDATE(), 101) --mm/dd/yyyy 00:00:00.000SET @ThruDate = DATEADD(DAY, 1, @FromDate) SET @ThruDate = DATEADD(MS, -3, @ThruDate) --mm/dd/yyyy 23:59:59.997SELECT * FROM Employee WHERE HireDate BETWEEN @FromDate AND @ThruDateUsing -3 millliseconds will give you the maximum time Sql Server can use.

RE: Comparison of Dates in SQL

TheSQLGuru — Fri, 01 May 2009 07:40:49 GMT

[quote][b]Ashish Pathak (5/1/2009)[/b][hr]I have gone through the entore thread. Could you please tell me how my solution is so suboptimal. Will it not work in any condition or will it be having a bad impact on performance. Just for better of my understanding... Thanks,Ashish[/quote]Like Paul said, the problem is that using a function on a column almost always prevents the use of an index seek (if an index is useable) and the engine must resort to an index scan (i.e. reading ALL of the index to find values, as opposed to very efficiently seeking down the btree to get the required rows). Another, sometimes incredibly damaging, issue is that statistics aren't usable either - which can lead to horribly inefficient query plans overall.I will say that in many, if not most, cases this issue isn't even noticed. There may not even be an index available, in which case all queries would have to use a table scan. Or the where clause would hit such a large portion of the rows that an index scan is more efficient. Or there simply aren't many rows in the table in which case an index seek might take 3 reads whereas a scan would take 100 - simply not a noticeable difference in most cases. But if you have 100M rows an index seek might take 5 or 6 reads but the scan could take tens of thousands - and THAT will leave a mark. :)Having said all that, there is still NO EXCUSE for writing suboptimal code like this once you know how to do it correctly. Yes, sometimes you have to be expedient in your coding to hit a deadline and taking shortcuts can allow you to write code faster. But this isn't one of those situations. It takes marginally more (if any) effort to do it correctly in this case.

RE: Comparison of Dates in SQL

Paul White — Fri, 01 May 2009 07:05:29 GMT

RE: Comparison of Dates in SQL

Ashish Pathak — Fri, 01 May 2009 06:39:41 GMT

I have gone through the entore thread. Could you please tell me how my solution is so suboptimal. Will it not work in any condition or will it be having a bad impact on performance. Just for better of my understanding... Thanks,Ashish

RE: Comparison of Dates in SQL

Paul White — Thu, 30 Apr 2009 15:58:51 GMT

[quote][b]TheSQLGuru (4/30/2009)[/b][hr]Reread my post Paul - I said using a function on a COLUMN. Your example is using a function on a VARIABLE. Just a slight difference there. :w00t:[/quote]Ah. Yes. Well. :blush:A million apologies, deduct 100 smart-*** points from me!Maybe the next version of SQL Server will be able to constant-fold entire columns :laugh:Paul

RE: Comparison of Dates in SQL

TheSQLGuru — Thu, 30 Apr 2009 15:52:38 GMT

[quote][b]Paul White (4/30/2009)[/b][hr][quote][b]TheSQLGuru (4/30/2009)[/b][hr]...there is also the same issue of index scan/seek if you are using a function on a column in a where clause.[/quote]Unless you use the solution posted by Lynn and others:wherereferencedate >= dateadd(dd, datediff(dd, 0, @comparedate), 0) and -- Beginning of this dayreferencedate < dateadd(dd, datediff(dd, 0, @comparedate) + 1, 0) -- Beginning of next dayAFAIK dateadd and datediff are functions :-PGenerally it is true though!Paul[/quote]Reread my post Paul - I said using a function on a COLUMN. Your example is using a function on a VARIABLE. Just a slight difference there. :w00t:

RE: Comparison of Dates in SQL

Paul White — Thu, 30 Apr 2009 14:53:07 GMT

[quote][b]TheSQLGuru (4/30/2009)[/b][hr]...there is also the same issue of index scan/seek if you are using a function on a column in a where clause.[/quote]Unless you use the solution posted by Lynn and others:wherereferencedate >= dateadd(dd, datediff(dd, 0, @comparedate), 0) and -- Beginning of this dayreferencedate < dateadd(dd, datediff(dd, 0, @comparedate) + 1, 0) -- Beginning of next dayAFAIK dateadd and datediff are functions :-PGenerally it is true though!Paul

RE: Comparison of Dates in SQL

Paul White — Thu, 30 Apr 2009 14:48:32 GMT

[quote][b]Amber.Brouillard (4/30/2009)[/b][hr]Nevermind, as I look at it closer, I see your point. Thanks for the correction.[/quote]Saints be praised ;-)

RE: Comparison of Dates in SQL

Amber.Brouillard — Thu, 30 Apr 2009 13:50:21 GMT

Yeah, I saw that after I posted my response. Thanks!

RE: Comparison of Dates in SQL

Goldie Lesser — Thu, 30 Apr 2009 13:42:06 GMT

[quote][b]TheSQLGuru (4/30/2009)[/b][hr][quote][b]Goldie Graber (4/30/2009)[/b][hr][quote][b]Amber.Brouillard (4/30/2009)[/b][hr]Hi Jeff,The dates I use do have a time with them (that is how SQL stores them) and dateadd(day, 1, date) still works fine.Thanks!Amber[/quote]If your dates have times with them (which all DATETIME data does in SQL Server) then your example is NOT accurate. It will not work properly.Take a look at this code.The goal is to retrieve all data up until a given day. If we do it your way it does not work properly.[code]DECLARE @CurrentDate DATETIMESET @CurrentDate = GETUTCDATE()DECLARE @Sample TABLE( SomeID INT IDENTITY(1,1) ,SomeDate DATETIME)INSERT INTO @SampleSELECT '2009-04-30 18:29'UNION ALL SELECT '2009-04-30 10:29'UNION ALL SELECT '2009-04-30 19:29'UNION ALL SELECT '2009-04-30 20:29'UNION ALL SELECT '2009-04-30 00:00'UNION ALL SELECT '2009-04-30 18:30'UNION ALL SELECT '2009-04-29 10:29'UNION ALL SELECT '2009-04-29 11:05'UNION ALL SELECT '2009-04-29 05:13'SELECT *FROM @SampleWHERE SomeDate <= DATEADD(DAY, 1, @CurrentDate) AND SomeDate >= @CurrentDate[/code][/quote]Amber, there is no argument here. This thread is about getting all records through a given day. Your method simply fails to accomplish that given certain time values as part of the date field. Sorry, but that is not debatable - it is a fact. IF you ALWAYS get the right answer with your method then a) your objective is not the same as this thread's, b) you don't have time values such as exhibited here or c) you really are getting incorrect data but aren't aware of it.There is also the same issue of index scan/seek if you are using a function on a column in a where clause.[/quote]Let's take a closer look.Run this query to see how many records there are per day[code]SELECT *FROM @SampleORDER BY SomeDate[/code]There are 13 records in the file and the breakdown is as follows:04/29/2009 - 3 records04/30/2009 - 6 records05/01/2009 - 4 recordsAccordingly, if we want data from 4/29 and 5/1, 10 records should be returned.However your query only returns 7 records and the breakdown is as follows04/30/2009 - 4 records05/01/2009 - 3 recordsYou are missing 3 records ;-)

RE: Comparison of Dates in SQL

Amber.Brouillard — Thu, 30 Apr 2009 13:33:06 GMT

Nevermind, as I look at it closer, I see your point. Thanks for the correction.Blessings!Amber

RE: Comparison of Dates in SQL

Amber.Brouillard — Thu, 30 Apr 2009 13:29:17 GMT

DECLARE @CurrentDate DATETIMESET @CurrentDate = GETDATE() DECLARE @Sample TABLE( SomeID INT IDENTITY(1,1) ,SomeDate DATETIME) INSERT INTO @SampleSELECT '2009-04-30 18:29'UNION ALL SELECT '2009-04-30 10:29'UNION ALL SELECT '2009-04-30 19:29'UNION ALL SELECT '2009-04-30 20:29'UNION ALL SELECT '2009-04-30 00:00'UNION ALL SELECT '2009-04-30 18:30'UNION ALL SELECT '2009-04-29 10:29'UNION ALL SELECT '2009-04-29 11:05'UNION ALL SELECT '2009-04-29 05:13'----added datesUNION ALL SELECT '2009-05-01 18:30'UNION ALL SELECT '2009-05-01 10:29'UNION ALL SELECT '2009-05-01 11:05'UNION ALL SELECT '2009-05-01 05:13'SELECT *FROM @SampleWHERE SomeDate BETWEEN @CurrentDate AND DATEADD(DAY, 1, @CurrentDate)With this, I get both today's date (4/30/09) AND all of the records with tomorrow's date.Blessings!Amber

RE: Comparison of Dates in SQL

TheSQLGuru — Thu, 30 Apr 2009 13:04:26 GMT

[quote][b]Goldie Graber (4/30/2009)[/b][hr][quote][b]Amber.Brouillard (4/30/2009)[/b][hr]Hi Jeff,The dates I use do have a time with them (that is how SQL stores them) and dateadd(day, 1, date) still works fine.Thanks!Amber[/quote]If your dates have times with them (which all DATETIME data does in SQL Server) then your example is NOT accurate. It will not work properly.Take a look at this code.The goal is to retrieve all data up until a given day. If we do it your way it does not work properly.[code]DECLARE @CurrentDate DATETIMESET @CurrentDate = GETUTCDATE()DECLARE @Sample TABLE( SomeID INT IDENTITY(1,1) ,SomeDate DATETIME)INSERT INTO @SampleSELECT '2009-04-30 18:29'UNION ALL SELECT '2009-04-30 10:29'UNION ALL SELECT '2009-04-30 19:29'UNION ALL SELECT '2009-04-30 20:29'UNION ALL SELECT '2009-04-30 00:00'UNION ALL SELECT '2009-04-30 18:30'UNION ALL SELECT '2009-04-29 10:29'UNION ALL SELECT '2009-04-29 11:05'UNION ALL SELECT '2009-04-29 05:13'SELECT *FROM @SampleWHERE SomeDate <= DATEADD(DAY, 1, @CurrentDate) AND SomeDate >= @CurrentDate[/code][/quote]Amber, there is no argument here. This thread is about getting all records through a given day. Your method simply fails to accomplish that given certain time values as part of the date field. Sorry, but that is not debatable - it is a fact. IF you ALWAYS get the right answer with your method then a) your objective is not the same as this thread's, b) you don't have time values such as exhibited here or c) you really are getting incorrect data but aren't aware of it.There is also the same issue of index scan/seek if you are using a function on a column in a where clause.

RE: Comparison of Dates in SQL

Goldie Lesser — Thu, 30 Apr 2009 12:51:43 GMT

[quote][b]Amber.Brouillard (4/30/2009)[/b][hr]I'm not going to argue with you about this. I've been using dateadd(day, 1, date) for about six months and I use it in many different programs and stored procedures. It has worked fine for me without any issues. I do not know why it doesn't work for you, but it has consistently worked for me (and continues to work).Blessings!Amber[/quote]Nobody is trying to argue with you.Just trying to show you, that from many years experience, it simply does not work.I suggest you take a closer look at your code, it may fail in some cases and it's better to catch it now then later. :-)

RE: Comparison of Dates in SQL

Amber.Brouillard — Thu, 30 Apr 2009 12:46:00 GMT

I'm not going to argue with you about this. I've been using dateadd(day, 1, date) for about six months and I use it in many different programs and stored procedures. It has worked fine for me without any issues. I do not know why it doesn't work for you, but it has consistently worked for me (and continues to work).Blessings!Amber

RE: Comparison of Dates in SQL

Goldie Lesser — Thu, 30 Apr 2009 12:38:20 GMT

[quote][b]Amber.Brouillard (4/30/2009)[/b][hr]Hi Jeff,The dates I use do have a time with them (that is how SQL stores them) and dateadd(day, 1, date) still works fine.Thanks!Amber[/quote]If your dates have times with them (which all DATETIME data does in SQL Server) then your example is NOT accurate. It will not work properly.Take a look at this code.The goal is to retrieve all data up until a given day. If we do it your way it does not work properly.[code]DECLARE @CurrentDate DATETIMESET @CurrentDate = GETUTCDATE()DECLARE @Sample TABLE( SomeID INT IDENTITY(1,1) ,SomeDate DATETIME)INSERT INTO @SampleSELECT '2009-04-30 18:29'UNION ALL SELECT '2009-04-30 10:29'UNION ALL SELECT '2009-04-30 19:29'UNION ALL SELECT '2009-04-30 20:29'UNION ALL SELECT '2009-04-30 00:00'UNION ALL SELECT '2009-04-30 18:30'UNION ALL SELECT '2009-04-29 10:29'UNION ALL SELECT '2009-04-29 11:05'UNION ALL SELECT '2009-04-29 05:13'SELECT *FROM @SampleWHERE SomeDate <= DATEADD(DAY, 1, @CurrentDate) AND SomeDate >= @CurrentDate[/code]

RE: Comparison of Dates in SQL

Amber.Brouillard — Thu, 30 Apr 2009 11:53:32 GMT

Hi Jeff,The dates I use do have a time with them (that is how SQL stores them) and dateadd(day, 1, date) still works fine.Thanks!Amber

RE: Comparison of Dates in SQL

TheSQLGuru — Thu, 30 Apr 2009 07:43:35 GMT

[quote][b]Ashish Pathak (4/30/2009)[/b][hr]Posted comment on Comparision of Dates in SQL looks fine. But as written above, it may not be the best solution in case of using indexes. Well... while comparing two datetime I prefer to use below:SELECT Count(EmpID)FROM EmployeeWHERE Convert(Varchar(8), EnteredDate, 112) >= Convert(Varchar(8), Date1, 112) Convert(Varchar(8), EnteredDate, 112) <= Convert(Varchar(8), Date2, 112)Above will give the desired output.Regards,Ashish[/quote]Once again a post that voids the use of index seeks to resolve the query. Ashish, please review this entire thread to determine why you present a suboptimal solution.

RE: Comparison of Dates in SQL

Ashish Pathak — Thu, 30 Apr 2009 03:26:13 GMT

Posted comment on Comparision of Dates in SQL looks fine. But as written above, it may not be the best solution in case of using indexes. Well... while comparing two datetime I prefer to use below:SELECT Count(EmpID)FROM EmployeeWHERE Convert(Varchar(8), EnteredDate, 112) >= Convert(Varchar(8), Date1, 112) Convert(Varchar(8), EnteredDate, 112) <= Convert(Varchar(8), Date2, 112)Above will give the desired output.Regards,Ashish

RE: Comparison of Dates in SQL

Paul White — Wed, 29 Apr 2009 21:12:36 GMT

[quote][b]Soki (4/29/2009)[/b][hr]Actually yes, when dealing with .... blah blah blah[/quote]Soki,I don't have time for a comprehensive reply, so I would just invite you to read the other posts on this thread - especially those from Jeff Moden and Lynn Pettis. You should be able to find a few problems with the reply you posted :-)Paul

RE: Comparison of Dates in SQL

Jeff Moden — Wed, 29 Apr 2009 20:41:33 GMT

[quote][b]Amber.Brouillard (4/29/2009)[/b][hr]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.[/quote]If the date has a time with it, that could be a problem.

RE: Comparison of Dates in SQL

Jeff Moden — Wed, 29 Apr 2009 20:40:14 GMT

Nope... 23:59:59 misses almost a whole second of the day. The >= and < method is probably the best method there is.

RE: Comparison of Dates in SQL

Soki — Wed, 29 Apr 2009 19:59:05 GMT

[quote][b]benr (4/28/2009)[/b][hr]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.[/quote]Actually yes, when dealing with comparison against fixed values (ie: variables) it's a [b]TERRIBLE[/b] idea to use functions on the columns, functions create a lot of overhead and block the indexes from being used properly, (I'm actually guessing you might even loose SARGability), the best way to do this is to make the vars hold values that will remove the function from the query, one way is to use the < value and add a date (assuming you're leaving the hour at 00:00:00 otherwise you'll screw your filter) or even to calculate to the date you want and time as 23:59:59 since you only do this once, the overhead performance is almost zero.Another good way to handle dates in case you're interested in performance (for example on DWH) is to use Julian dates, this is the fastest way to retrieve datas from huge tables.

RE: Comparison of Dates in SQL

Amber.Brouillard — Wed, 29 Apr 2009 12:23:15 GMT

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.

RE: Comparison of Dates in SQL

TheSQLGuru — Wed, 29 Apr 2009 08:54:47 GMT

[quote][b]itamar (4/29/2009)[/b][hr]How about [url=http://en.wikipedia.org/wiki/Microsoft_SQL_Server_Compare_Tools]different kind of SQL compare tools[/url], they work OK with dates?[/quote]when using comparison tools one is most often comparing the entire table anyway, so index scan/seek issues are moot.

RE: Comparison of Dates in SQL

TheSQLGuru — Wed, 29 Apr 2009 08:53:19 GMT

[quote][b]rob.lobbe (4/28/2009)[/b][hr][quote][b]TheSQLGuru (4/28/2009)[/b][hr][quote]Again this code will break with new, more precise, datetime datatypes in SQL Server 2008.[/quote]Well if you change the code - ie the data type - of course you need to make adjustments.For a datatype of [b]datetime[/b] 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 [i]most[/i] reporting requirements.[/quote]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.

RE: Comparison of Dates in SQL

itamar-863608 — Wed, 29 Apr 2009 07:37:57 GMT

How about [url=http://en.wikipedia.org/wiki/Microsoft_SQL_Server_Compare_Tools]different kind of SQL compare tools[/url], they work OK with dates?