February 9, 2012 at 10:40 am
I am querying a database with values recorded every 15 minutes. But its not exactly every 15 minutes sometimes its a few seconds over.
The results I am trying to get is First day of Last month -6 hours (6:00 PM) through Last day of Last Month 6:00 PM.
This works well with one small problem because the last value is reported at 6:00:05 so it only returns the 5:45:05 value.
WHERE TIMESTAMP >= DATEADD(hh, -6, DATEADD(mm, DATEDIFF(mm,0,GETDATE()) -1, 0))
AND TIMESTAMP < DATEADD(hh, -6, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))
Output for this query is 12/31/11 6:00:04 PM - 1/31/12 5:45:05.
I need to add 60 seconds to the end value. I tried another DATEADD but have been unsuccessful nesting it another level.
Any suggestions?
February 9, 2012 at 12:13 pm
print dateadd( ss, 60, DATEADD(hh, -6, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)))
OR
print dateadd( n, 1, DATEADD(hh, -6, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)))
n = minute, m = month.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 9, 2012 at 12:40 pm
Instead of adding a fudge-factor to the math, why not do something like this:
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T ;
CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
MyDateColumn DATETIME NOT NULL) ;
INSERT INTO #T
(MyDateColumn)
VALUES ('1/31/12 5:45 pm'),
('1/31/12 6:02 pm'),
('12/31/11 5:45 pm'),
('12/31/11 6:00:05 pm') ;
SELECT *
FROM #T
WHERE MyDateColumn >= (SELECT MIN(MyDateColumn)
FROM #T
WHERE MyDateColumn >= DATEADD(HOUR, -6,
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -1, GETDATE())),
0)))
AND MyDateColumn <= (SELECT MIN(MyDateColumn)
FROM #T
WHERE MyDateColumn >= DATEADD(HOUR, -6, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)))
ORDER BY #T.MyDateColumn ;
What that does is have the sub-queries calculate the correct start and end for you, without you having to worry about the frequency or duration of the job that's populating the table.
That way, if the job is delayed for some reason, the query will still get the right records, even if it is more than 1 minute late.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2012 at 12:17 pm
This worked perfect
print dateadd( ss, 60, DATEADD(hh, -6, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)))
I was so close of course forgot the last ) at the end!
Thank you
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply