Author |
Topic |
terbs
Starting Member
29 Posts |
Posted - 2006-11-25 : 23:22:32
|
hello, im abit of a SQL newbie just need some help to retrieve items where the date = today. The format of the field is DateTime(2006-11-11 11:00:00) but I have no idea how to select items just by date, no time. Although retrieving the time would be handy.here is the "code" I have, I know the WHERE clause is completly off the money.. but its basically what i need...CREATE PROCEDURE dbo.sproc_TodaysOrdersAS SELECT tblOrder.OrderID, tblOrder.UserID, tblOrder.Price, tblOrder.Status, tblOrder.TimeOrder, tblOrder.TimeNeededFROM tblOrderWHERE tblOrder.TimeNeeded = today()RETURN |
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-25 : 23:40:59
|
There are various ways to do it, here's a popular oneWHERE tblOrder.TimeNeeded = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)If the value in your column has a time, then you'll need to ignore that too, which you could do the same wayWHERE DATEADD(day, DATEDIFF(day, 0, tblOrder.TimeNeeded), 0) = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)But because it essentially means your column holds a value that lies between midnight of today and a moment before midnight tomorrow, you can do it this way and this would typically perform betterWHERE tblOrder.TimeNeeded >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND tblOrder.TimeNeeded < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) |
|
|
terbs
Starting Member
29 Posts |
Posted - 2006-11-25 : 23:56:18
|
cheers snSQL, works a charm : ) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-26 : 04:27:43
|
Today's orders only?WHERE DATEDIFF(day, tblOrder.TimeNeeded, GETDATE()) = 0But no indexes can be used.WHERE tblOrder.TimeNeeded >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)AND tblOrder.TimeNeeded < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)Now any index for TimeNeeded column can be used.Peter LarssonHelsingborg, Sweden |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-26 : 12:25:25
|
Assuming no index to take advantage of, in my experience the calculated style:WHERE DATEDIFF(day, tblOrder.TimeNeeded, GETDATE()) = 0uses about twice the CPU cycles compared to the Range styleWHERE tblOrder.TimeNeeded >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)AND tblOrder.TimeNeeded < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)Kristen |
|
|
|
|
|