Date & Time Query

Using a Stored Procedure I'd like to return a recordset with records for a
DATE range (like a month or week) but only records that happened between a
given TIME range (like between 9:00 AM and 9:30 AM).
Example Table Fields: RecordID, UpdateDateTime, Description, UserName
This is looking over thousands of records.
Unfortunately, this will negate any index you have, but here is one way:
DECLARE @.startDT SMALLDATETIME,
@.endDT SMALLDATETIME,
@.startTM SMALLDATETIME,
@.endTM SMALLDATETIME
SET @.startDT = '20040901'
SET @.endDT = '20040914'
SET @.startTM = '09:00 AM'
SET @.endTM = '09:30 AM'
SELECT * FROM
(
SELECT col1, col2, dt =
CONVERT
(
SMALLDATETIME,
CONVERT(CHAR(8), UpdateDateTime, 108)
)
FROM tbl
WHERE UpdateDateTime >= @.startDT
AND UpdateDateTime < (@.endDT + 1)
)
x
WHERE x.dt >= @.startTM
AND x.dt <= @.endTM
http://www.aspfaq.com/
(Reverse address to reply.)
"alien_alan" <alienalan@.discussions.microsoft.com> wrote in message
news:74F8013E-451D-40CE-A69D-18B2D017EF4A@.microsoft.com...
> Using a Stored Procedure I'd like to return a recordset with records for a
> DATE range (like a month or week) but only records that happened between a
> given TIME range (like between 9:00 AM and 9:30 AM).
> Example Table Fields: RecordID, UpdateDateTime, Description, UserName
> This is looking over thousands of records.
|||One way of doing it: Assuming March 2004:
WHERE dtcol >= '20040301' AND < '20040401'
AND CAST(CONVERT(char(8), dtcol, 108) AS datetime) BETWEEN '19000101 09:00' AND '19000101 09:30'
Note that with above solution, SQL Server can use an index on the column to narrow down the rows
based on the date range.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alien_alan" <alienalan@.discussions.microsoft.com> wrote in message
news:74F8013E-451D-40CE-A69D-18B2D017EF4A@.microsoft.com...
> Using a Stored Procedure I'd like to return a recordset with records for a
> DATE range (like a month or week) but only records that happened between a
> given TIME range (like between 9:00 AM and 9:30 AM).
> Example Table Fields: RecordID, UpdateDateTime, Description, UserName
> This is looking over thousands of records.
|||> Note that with above solution, SQL Server can use an index on the column
to narrow down the rows
> based on the date range.
FWIW, my subquery version can do that also... just can't use an index
against those outer results.
http://www.aspfaq.com/
(Reverse address to reply.)