April 29, 2009 at 7:59 pm
benr (4/28/2009)
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.
Actually yes, when dealing with comparison against fixed values (ie: variables) it's a TERRIBLE 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.
April 29, 2009 at 8:40 pm
Nope... 23:59:59 misses almost a whole second of the day. The >= and < method is probably the best method there is.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2009 at 8:41 pm
Amber.Brouillard (4/29/2009)
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.
If the date has a time with it, that could be a problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2009 at 9:12 pm
Soki (4/29/2009)
Actually yes, when dealing with .... blah blah blah
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
April 30, 2009 at 3:26 am
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
Employee
WHERE
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
April 30, 2009 at 7:43 am
Ashish Pathak (4/30/2009)
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
Employee
WHERE
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
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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 30, 2009 at 11:53 am
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
April 30, 2009 at 12:38 pm
Amber.Brouillard (4/30/2009)
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
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.
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETUTCDATE()
DECLARE @Sample TABLE
(
SomeID INT IDENTITY(1,1)
,SomeDate DATETIME
)
INSERT INTO @Sample
SELECT '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 @Sample
WHERE SomeDate = @CurrentDate
April 30, 2009 at 12:46 pm
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
April 30, 2009 at 12:51 pm
Amber.Brouillard (4/30/2009)
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
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. 🙂
April 30, 2009 at 1:04 pm
Goldie Graber (4/30/2009)
Amber.Brouillard (4/30/2009)
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
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.
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETUTCDATE()
DECLARE @Sample TABLE
(
SomeID INT IDENTITY(1,1)
,SomeDate DATETIME
)
INSERT INTO @Sample
SELECT '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 @Sample
WHERE SomeDate = @CurrentDate
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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 30, 2009 at 1:29 pm
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETDATE()
DECLARE @Sample TABLE
(
SomeID INT IDENTITY(1,1)
,SomeDate DATETIME
)
INSERT INTO @Sample
SELECT '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 dates
UNION 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 @Sample
WHERE 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
April 30, 2009 at 1:33 pm
Nevermind, as I look at it closer, I see your point. Thanks for the correction.
Blessings!
Amber
April 30, 2009 at 1:42 pm
TheSQLGuru (4/30/2009)
Goldie Graber (4/30/2009)
Amber.Brouillard (4/30/2009)
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
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.
DECLARE @CurrentDate DATETIME
SET @CurrentDate = GETUTCDATE()
DECLARE @Sample TABLE
(
SomeID INT IDENTITY(1,1)
,SomeDate DATETIME
)
INSERT INTO @Sample
SELECT '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 @Sample
WHERE SomeDate = @CurrentDate
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.
Let's take a closer look.
Run this query to see how many records there are per day
SELECT *
FROM @Sample
ORDER BY SomeDate
There are 13 records in the file and the breakdown is as follows:
04/29/2009 - 3 records
04/30/2009 - 6 records
05/01/2009 - 4 records
Accordingly, 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 follows
04/30/2009 - 4 records
05/01/2009 - 3 records
You are missing 3 records 😉
April 30, 2009 at 1:50 pm
Yeah, I saw that after I posted my response. Thanks!
Viewing 15 posts - 76 through 90 (of 110 total)
You must be logged in to reply to this topic. Login to reply