Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Select Items WHERE date=today

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_TodaysOrders

AS SELECT tblOrder.OrderID, tblOrder.UserID, tblOrder.Price, tblOrder.Status, tblOrder.TimeOrder, tblOrder.TimeNeeded

FROM tblOrder

WHERE 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 one

WHERE 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 way

WHERE 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 better

WHERE tblOrder.TimeNeeded >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND tblOrder.TimeNeeded < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
Go to Top of Page

terbs
Starting Member

29 Posts

Posted - 2006-11-25 : 23:56:18
cheers snSQL, works a charm : )
Go to Top of Page

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()) = 0

But 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 Larsson
Helsingborg, Sweden
Go to Top of Page

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()) = 0

uses about twice the CPU cycles compared to the Range style

WHERE tblOrder.TimeNeeded >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
AND tblOrder.TimeNeeded < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

Kristen
Go to Top of Page
   

- Advertisement -