query with time period spanning two days

I would like to run queries with data that sometimes span two days. The queries require start and end dates as well as start and end times. The following code works fine if the start time is less than the end time:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '09:00:00' and

'17:00:00' order by [DateTime]

However, if I try to run a query where the start time is greater than the end time (e.g., start time 5:00pm on one day until 9:00am the next day), the query returns an empty table.

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and convert(varchar, [DateTime],114) between '17:00:00' and

'09:00:00' order by [DateTime]

I need a way to indicate that the start and end times span two days. Can anybody help with this?

I think I found the answer:

select * from tst01 where convert(varchar, [DateTime],126) between '2005-09-15' and

'2006-01-27' and ((convert(varchar, [DateTime],114) between '17:00:00' and

'23:59:59') or (convert(varchar, [DateTime],114) between '00:00:00' and

'09:00:00')) order by [DateTime]

|||

I'm not sure exactly what you're after, but you should investigate the DATEADD() or DATEDIFF() functions. They will handle the day differences for you.

CREATE TABLE #T1 (StartTime datetime, EndTime datetime)

INSERT INTO #T1

VALUES ('2000-01-01 8:00', '2000-01-01 8:05')

INSERT INTO #T1

VALUES ('2000-01-02 8:00', '2000-01-01 7:59')

SELECT DATEDIFF(hh, EndTime, StartTime)

FROM #T1

You may have to play with either of these functions if you want the full year, month, day, etc., but you just string them together to get that.

|||

I have numerous tables which contain data recorded over a 24hr period for several years. The tables each contain a column called [DateTime] (for historic reasons), as well as several other columns containing data recorded during those datetimes (in each record).

At times, it is necessary to examine data recorded during non-business hours (e.g., 5:00pm to 9:00am the following day). Hence, the start time (17:00:00 ) is greater than the end time (09:00:00) which falls on the following date (see code in posts above). The code in my second posting while a bit of a kludge seems to work. I'm not sure how using dateadd or datediff would help. But, thanks anyway.

|||I see. DATEADD() or DATEDIFF() will handle the day span problem for you, since they are aware (as shown in the example) that a day has passed between two hour or minute comparisons. You can use that logic to find and process the rows that have that condition.