I am trying to get al the rows from table1 where datetime is between 9:00AM
yesterday and 9:00AM today if the time now is less than 9:00AM. Otherwise it
should return all where datetime>9:00 AM today.

Is this possible as a query in sql2000?

Fred (Fred@hotmail.com) writes:
> I am trying to get al the rows from table1 where datetime is between
> 9:00AM yesterday and 9:00AM today if the time now is less than 9:00AM.
> Otherwise it should return all where datetime>9:00 AM today.
>

SELECT ...
FROM tbl
WHERE datepart(hour, getdate()) >= 9 AND
datecol >= convert(char(8), getdate(), 112) + ' 09:00:00'
OR datepart(hour, getdate()) < 9 AND
datecol BETWEEN datediff(day, -1,
convert(char(8), getdate(), 112) + ' 09:00:00') AND
convert(char(8), getdate(), 112) + ' 09:00:00'

The recurring expression

convert(char(8), getdate(), 112) + ' 09:00:00'

could be put in a function for shorter code. However, this could be
expensive performancewise.

The above is not tested.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

"Fred" <Fred@hotmail.com> wrote in message news:INvAc.32931$sj4.2639@news-server.bigpond.net.au...
> I am trying to get al the rows from table1 where datetime is between 9:00AM
> yesterday and 9:00AM today if the time now is less than 9:00AM. Otherwise it
> should return all where datetime>9:00 AM today.
>
> Is this possible as a query in sql2000?

Assume table T and datetime column dt.

SELECT *
FROM T
WHERE DATEPART(HOUR, CURRENT_TIMESTAMP) < 9 AND
dt BETWEEN
DATEADD(HOUR,
9,
CONVERT(CHAR(8), CURRENT_TIMESTAMP - 1, 112))
AND
DATEADD(HOUR,
9,
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112))
UNION ALL
SELECT *
FROM T
WHERE DATEPART(HOUR, CURRENT_TIMESTAMP) >= 9 AND
dt > DATEADD(HOUR,
9,
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112))

or, alternatively,

SELECT *
FROM T
WHERE (DATEPART(HOUR, CURRENT_TIMESTAMP) >= 9 OR
(dt BETWEEN
DATEADD(HOUR,
9,
CONVERT(CHAR(8), CURRENT_TIMESTAMP - 1, 112))
AND
DATEADD(HOUR,
9,
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112))))
AND
(DATEPART(HOUR, CURRENT_TIMESTAMP) < 9 OR
dt > DATEADD(HOUR,
9,
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112)))

--
JAG

Thank you Erland and John. Perfect!

"John Gilson" <jag@acm.org> wrote in message
news:t_wAc.83252$mX.27772407@twister.nyc.rr.com...
> "Fred" <Fred@hotmail.com> wrote in message
news:INvAc.32931$sj4.2639@news-server.bigpond.net.au...
> > I am trying to get al the rows from table1 where datetime is between
9:00AM
> > yesterday and 9:00AM today if the time now is less than 9:00AM.
Otherwise it
> > should return all where datetime>9:00 AM today.
> >
> > Is this possible as a query in sql2000?
>
> Assume table T and datetime column dt.
>
> SELECT *
> FROM T
> WHERE DATEPART(HOUR, CURRENT_TIMESTAMP) < 9 AND
> dt BETWEEN
> DATEADD(HOUR,
> 9,
> CONVERT(CHAR(8),
CURRENT_TIMESTAMP - 1, 112))
> AND
> DATEADD(HOUR,
> 9,
> CONVERT(CHAR(8),
CURRENT_TIMESTAMP, 112))
> UNION ALL
> SELECT *
> FROM T
> WHERE DATEPART(HOUR, CURRENT_TIMESTAMP) >= 9 AND
> dt > DATEADD(HOUR,
> 9,
> CONVERT(CHAR(8),
CURRENT_TIMESTAMP, 112))
>
> or, alternatively,
>
> SELECT *
> FROM T
> WHERE (DATEPART(HOUR, CURRENT_TIMESTAMP) >= 9 OR
> (dt BETWEEN
> DATEADD(HOUR,
> 9,
> CONVERT(CHAR(8),
CURRENT_TIMESTAMP - 1, 112))
> AND
> DATEADD(HOUR,
> 9,
> CONVERT(CHAR(8), CURRENT_TIMESTAMP,
112))))
> AND
> (DATEPART(HOUR, CURRENT_TIMESTAMP) < 9 OR
> dt > DATEADD(HOUR,
> 9,
> CONVERT(CHAR(8),
CURRENT_TIMESTAMP, 112)))
>
> --
> JAG
>
>