Time syntax in SQL

[Lowenbraudark]

Our standard 24 hour operational day runs starts at 9:00AM (as opposed to
12:00AM). I have a stored procedure (w/ SQL 2K backend) that lets the user
input a date and then it displays the associated records. However, with the
combined datatime field in SQL I cannont figure out how to constrict the
records for our 9:00 shifts. The table currently has a separate column for
time that would be used.

DateField Time Other Columns
------------ ------ ----- ----------
11/12/05 07:30 Blah Blah
11/12/05 15:20 Blah Blah
11/13/05 06:29 Blah Blah

So basically the end user should type in "11/12/05" and all of records from
11/12/05 starting at 9:00AM until 11/13/05 at (or less than) 9:00 AM should
be returned. I would imagine it's something like:

ALTER PROCEDURE dbo.[Select Date]
(@Date datetime)
AS SELECT DateTime, TIME, Field1, Field1, Field1, Field1, Field1, primarykey
FROM dbo.[TableName]
WHERE (DateTime = @Date) AND (TIME >= CONVERT(DATETIME, '1899-12-30
09:00:00', 102)) OR
(DateTime = DATEADD(dd, 1, @Date)) AND (TIME <
CONVERT(DATETIME, '1899-12-30 09:00:00', 102))

I just can't get the syntax quite right. Thanks for the Help.

 

[Tim Ferguson]

So basically the end user should type in "11/12/05" and all of records
from 11/12/05 starting at 9:00AM until 11/13/05 at (or less than) 9:00
AM should be returned. I would imagine it's something like:

The real t-sql wizards are over in m.p.a.adpsqlserver, but here goes...

SELECT Records FROM ATable
WHERE CONVERT(VARCHAR, DATEADD('hh', -9, MyDateTime),1) = @MyInputDate


IOW, just subtract the nine hours and see if it's still the right date...

Hope that helps


Tim F