asked on

Query to return results in specific date and time range.

I am new to SQL. In SQL 2000 I need to create a query that returns data from a specific table within a specific date and time range. For example, the range would be 4:00AM 5/30 to 3:59AM 5/31. The report will be sent to run at 4:00 AM daily and needs to include all transactions from the past 24 hours. So from 4:00 AM 5/30 thru 3:59 AM 5/31. I would like to set the query so that every day that the report is run I don't have to change the query.

Thanks,

Locke

ASKER CERTIFIED SOLUTION

Sean McIlvenna

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

srafi78

If the report is run every day at the same time and you want the data for the past 24 hours then

Select * from urTable where dateValue Between GetDate() and GetDate() - 2 sould give you the data for the previous 24 hrs

If you want to run the query for the previous day then
Select * from urTable where dateValue between Convert(Varchar(10), getdate(), 101) and Convert(Varchar(10), getdate - 2, 101) should give the results for the previous day 12:00 AM - 11:59:59 PM

Sean McIlvenna

That won't work... Your starting number/date for a between statement (i just tested) has to be lower than your ending number/date.

srafi78

Okay, my bad the queries should be
Select * from urTable where dateValue Between GetDate() - 2 and GetDate() should give you the data for the previous 24 hrs

If you want to run the query for the previous day then
Select * from urTable where dateValue between Convert(Varchar(10), getdate() - 1, 101) and Convert(Varchar(10), getdate(), 101) should give the results for the previous day 12:00 AM - 11:59:59 PM

Locke Mackenzie

ASKER

What if I want to collect data from 4:00AM starting the previous day thru 3:59AM the current day? How do I adjust if I want to alter the time range. I am not always interested in 12:00AM to 11:59PM
Thanks,

Locke

SOLUTION

srafi78

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial