April 14, 2005 at 8:28 am
I have a date field in a table that I'm having trouble with. What I need to do is select all records that have today's date in said field.
The data looks like 2005-04-14 16:46:17:007
I tried select * from table where date_field = getDate() with no luck.
Anyone able to help out here? Thanks in advance!
April 14, 2005 at 9:01 am
Read BOL on the CONVERT() function and the date formatting codes.
Memorize format code 112 - ISO format with 4 digit year - YYYYMMDD.
Select * from table where CONVERT(varchar,date_field,112) = CONVERT(varchar,getDate(),112)
April 14, 2005 at 9:03 am
Select * from dbo.MyTable where date_field between dateadd(d,0, datediff(d, 0, getdate())) and dateadd(d,1, datediff(d, 0, getdate()))
April 14, 2005 at 9:04 am
PW's solution will work but it will force an index scan, my solution will allow for a range seek which can give a great performance boost.
April 14, 2005 at 9:07 am
For a complete discussion see this thread:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=171688#bm171793
Also check out this article for lots of info on dates and date comparisons:
http://www.karaszi.com/SQLServer/info_datetime.asp
April 14, 2005 at 9:11 am
Remi,
Will this work without a table/index scan?
where datediff(d, Getdate(), date_column) = 0
April 14, 2005 at 9:17 am
Nope, the datediff operation must be executed in order to check for equality to 0. There are really only a few functions that you can run that won't force a scan... Someone posted a link/ref about this a few days ago but I can't find it anywhere.
April 15, 2005 at 10:13 am
The getdate() function returns both the day and time parts of the current date. That is why your initial query failed. Converting to format 112 gives you only the date, which is what you want.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
April 15, 2005 at 10:24 am
Remi,
Your solution Select * from dbo.MyTable where date_field between dateadd(d,0, datediff(d, 0, getdate())) and dateadd(d,1, datediff(d, 0, getdate()))
returns date_field = to Getdate() and Getdate() + 1 (00:00:00.000)
Edited:
I think you ment the code to be: Select * from dbo.MyTable where date_field >= dateadd(d,0, datediff(d, 0, getdate()))
and date_field < dateadd(d,1, datediff(d, 0, getdate()))
April 15, 2005 at 4:30 pm
Using
SELECT * FROM Table_Name
WHERE DATEDIFF(d, Date_Field, GetDate()) = 0
gave me
Select, est. cost = 0.622
Clustered Index Scan, est. cost = 0.616
Est. row count = 2537
Both other methods were identical to each other, (and the above except for):
Select, est. cost = 0.623
Est. row count = 3114
This would seem to indicate that using the return of the DATEDIFF() with the date value and GETDATE() is just as efficient, if not ever slightly more so than the other methods.
April 16, 2005 at 6:11 am
You pretty much said it yourself... You have the same cost but with 33% more rows in the select so one option seems better than the other. While I can't retest my code here I'll always preffer a seek from a scan because I don't know how big the table may be in 10 years. And a clustered index scan on a 10 M rows table can't be faster than a single index seek on the same table.
April 16, 2005 at 9:01 am
I think Remi is correct. On a large table where the date you are looking for is a small percentage of the total rows then Remi's method will use an index seek and be magnitudes faster.
April 18, 2005 at 6:14 am
You're right ron k .. I forgot to take in consideration midnight on the 2nd day with the between operator.
April 18, 2005 at 8:17 am
I ran a test using these two statements in a batch:
Select my_date
from my_table
where my_date >= dateadd(d,0, datediff(d, 0, getdate()))
and my_date < dateadd(d,1, datediff(d, 0, getdate()))
SELECT my_date
FROM my_table
WHERE DATEDIFF(d, my_date, GetDate()) = 0
The cost of the first statement relative to the batch was 11.82% while the cost of the second statement relative to the batch was 88.18%
The first used an index seek and the second used an index scan.
Edit: The table contained 3200 rows.
Edit 2: A second test Selecting all columns in both statements caused both to use a Table Scan and a cost relative to the batch of very close to 50% each! ?
April 18, 2005 at 8:43 am
Strange, maybe the bookmark lookup cost was too high compared to the scan... Would have too see the ddl and the data to be more sure.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply