Dynamic Time Query -- sql server programming

ganesh
10/24/2007 9:25:00 PM

The below select (getdate()-1) all ways returns yesterday date

Try this

SELECT AL1.TDate, SUM ( AL1.NBR )
FROM dbo.Table AL1
WHERE AL1.TDate = select (getdate()-1)

[quoted text, click to view]

Razvan Socol
10/25/2007 1:26:12 AM

[quoted text, click to view]

To get today's date without the time, you can use:
DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
or:
CONVERT(datetime,CONVERT(char(8),GETDATE(),112))

To get yesterday's date, you can substract 1 from the above expression:

SELECT AL1.TDate, SUM ( AL1.NBR )
FROM dbo.Table AL1
WHERE AL1.TDate = DATEADD(d,DATEDIFF(d,0,GETDATE()),-1)

Note: I was assuming that you are storing only the date in the TDate
column. If you are storing the date and time in this column, you can
use something like this:

SELECT AL1.TDate, SUM ( AL1.NBR )
FROM dbo.Table AL1
WHERE AL1.TDate >= DATEADD(d,DATEDIFF(d,0,GETDATE()),-1)
AND AL1.TDate < DATEADD(d,DATEDIFF(d,0,GETDATE()),0)

See also: http://www.karaszi.com/SQLServer/info_datetime.asp

--
Razvan Socol

ibcarolek
10/25/2007 7:50:40 AM

[quoted text, click to view]

Razvan, you're my hero! Thank you!! Thank you too, Ganesh. However,
only Razvan's code work. I got a syntax error using "select".

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Dynamic Time Query