select between date

[Fred]

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?

[Erland Sommarskog]

Fred (Fr**@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, es****@sommarskog.se

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

[John Gilson]

"Fred" <Fr**@hotmail.com> wrote in message news:IN******************@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

[Fred]

Thank you Erland and John. Perfect!

"John Gilson" <ja*@acm.org> wrote in message
news:t_*********************@twister.nyc.rr.com...

"Fred" <Fr**@hotmail.com> wrote in message news:IN******************@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