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.

Query Timeout

I have a query I run in MSDE that will give me a timeout error message. It
reads:
"Timeout expired. The time out period elapsed prior to completion of the
operation or the server is not responding."
I know the server is responding, because the query seems to run anyway,
though I'm not sure the results are accurate. Is there any way to extend the
timeout period so this does not happen. The query running is adding
numerical records to one table based on criteria in the query and values in
another table.
What application are you using to query the database? If it's something your
wrote in-house and you are using ADO then set the CommandTimeout property of
the connection to 0 (zero). Some ADO libraries default to 30 seconds for a
timeout.
Jim
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:B8EFC32E-54F0-4CBB-A2F9-36699FA895B6@.microsoft.com...
> I have a query I run in MSDE that will give me a timeout error message.
It
> reads:
> "Timeout expired. The time out period elapsed prior to completion of the
> operation or the server is not responding."
> I know the server is responding, because the query seems to run anyway,
> though I'm not sure the results are accurate. Is there any way to extend
the
> timeout period so this does not happen. The query running is adding
> numerical records to one table based on criteria in the query and values
in
> another table.

query the amount of transactions in a period of time with sql server 2000

Is there a native tool (profile,trace,performance) feature I can use
to determine the amount of transactions that occur throughout the day?
Or is there a system table that keeps track of this(would be
preferable .. less strain on the system)?
I assume figuring out the transaction in a certain period will enable
me to calculate the busiest periods...I need to know the busiest
period of the day...how do I do this without putting an additional
strain on the server (can I use a different machine other than the
server to save a trace) ...I need to determine strain on
(processor,memory, and disk).
I also need to get a count on the largest number of users (running
transactions) on the server simultaneously.
Any help/advice would be deeply appreciatedFor this to be really meaingful, you should first define what you mean by
transactions. Your definition of transactions can impact your count of
transactions per second.
But if you just want to get a rough idea and the number of SQL requests from
non-apps (e.g. your Enterprise Manager, your monitoring tools, your cluster
service, etc)is relatively small compared to the SQL requests from your apps,
the perfmon counter batch Requests/sec under SQLServer:SQL Statistics can
give you pretty good idea as to how busy your SQL instance is and when. And
collecting the values of this counter is inexpensive.
Linchi
"tom booster" wrote:
> Is there a native tool (profile,trace,performance) feature I can use
> to determine the amount of transactions that occur throughout the day?
> Or is there a system table that keeps track of this(would be
> preferable .. less strain on the system)?
> I assume figuring out the transaction in a certain period will enable
> me to calculate the busiest periods...I need to know the busiest
> period of the day...how do I do this without putting an additional
> strain on the server (can I use a different machine other than the
> server to save a trace) ...I need to determine strain on
> (processor,memory, and disk).
> I also need to get a count on the largest number of users (running
> transactions) on the server simultaneously.
>
> Any help/advice would be deeply appreciated
>