September 2, 2008 at 4:48 pm
I have a script where I use the following code....
.
and a.dschrg_ts >= '09-01-2008 00:00:00'
and a.dschrg_ts <= '09-01-2008 23:59:59'
.
Of course I can manually change this to 9-2-08 tomorrow and run it. If I just want to run this script tomorrow without having to manually change the date (but keep the times), what syntax can I use?
I've done some searching and I've seen some examples of datediff() and getdate() but it's still not clicking on how I can use those functions for my specific situation. I just want yesterdays time range (midnight to 1 second before midnight tonight) to be used no matter what time of day I run this script today. By the way, dschrg_ts = discharge time stamp.
I eventually want to schedule this script to run every night which explains why I'd like to get help on figuring out syntax on these or other functions.
TIA,
John
September 2, 2008 at 5:08 pm
Here's one possibility:
select dateadd(dd, datediff(dd,0,Getdate()),0)- 1
select (dateadd(dd, datediff(dd,0,Getdate()),0)- 1)+ '23:59:59'
Greg
September 2, 2008 at 5:27 pm
That did the trick.
Thank you.
September 2, 2008 at 7:38 pm
The way you have your query written, you will miss any rows that fall in the last second of the day.
When you query for a date range, it is better to write the where clause in this form so that you do not have to worry about that.
where Date >= StartDateTime and Date < EndDateTime
With your query, you should do it like this to get everything from yesterday:
where a.dschrg_ts >= '20080901' and a.dschrg_ts < '20080902'
To automatically get yesterday, this would do it:
select
*
from
MyTable a
where
a.dschrg_ts >=
-- Start of yesterday
dateadd(dd,datediff(dd,0,Getdate())-1,0)and
a.dschrg_ts <
-- Start of Today
dateadd(dd,datediff(dd,0,Getdate()),0)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply