Query to return results in specific date and time range.

Locke Mackenzie
Locke Mackenzie used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr. Software Architect
Commented:
select * from TABLE where SOMEDATE between DateAdd( dd, -1, GetDate( ) ) And GetDate( )

Schedule that to be ran every day at a specific time and have the results emailed to you.

Commented:
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 McIlvennaSr. Software Architect

Commented:
That won't work... Your starting number/date for a between statement (i just tested) has to be lower than your ending number/date.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
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 MackenzieSF IT Support Tech

Author

Commented:
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
Commented:
Select * from urTable where dateValue between Convert(Varchar(10), getdate() - 1, 101) + ' ' + Convert(Varchar(8), '04:00:00', 108) and Convert(Varchar(10), getdate(), 101) + ' ' + Convert(Varchar(8), '03:59:59', 108)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial