October 13, 2010 at 11:18 am
After reading the post on how to run a query to get last months data (which worked great thanks Jeff Moden) I noticed one thing more I needed. I am collecting a data set that is reset at the beginning of each month. So I do not want to include the first entry because it is actually the total of last month, but I want to include the first entry for the following month. I got the second part by adding 15 seconds to the end but have been unsuccessful after many tries in subtracting the first 15 seconds and record.
Code used:
WHERE TSTAMP >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND TSTAMP < DATEADD(ss, +15, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))
Beginning of the month (I do not want to retrieve the first value)
TSTAMP: 2010-09-01 00:00:00.450 Value:53757.35
TSTAMP: 2010-09-01 00:15:00.450 Value:1.787687
References:
http://www.sqlservercentral.com/articles/Date+Manipulation/69694/
http://www.sqlservercentral.com/Forums/Topic677093-338-1.aspx
October 13, 2010 at 11:21 am
Finally got it! Right after I hit post Reply lol!
WHERE TSTAMP >= DATEADD(ss, +15, DATEADD(mm, DATEDIFF(mm,0,GETDATE()) -1, 0))
AND TSTAMP < DATEADD(ss, +15, DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0))
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply