February 15, 2011 at 8:57 am
I am looking for the best way to achieve a date conversion. I have a job that will run daily at any given time. I want to take the time it runs and build a condition where it works with data between the beginning and end of the month for that time period.
So if it ran on 2/15/2011 @ 10:11:59.... it would run a where of >= 2/1/2011 00:00:000 and <= 2/28/2011 23:59:59 (or < 3/1/2011 00:00:00).
Is the best way to do a datepart on the month and year and build it as a concatenate of converted dateparts?
February 15, 2011 at 9:06 am
Here's how I do that:
SELECT DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()), 0),
DATEADD(month, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
You can use a variable, parameter, or column instead of GetDate(), or use CurrentDateTime if you prefer that.
It takes the number of months since a given date (zero in this case, which is default for 1/1/1900 in DateTime), to a given date (getdate()), and finds out how many months, then adds that many months to 0 again. This also works for rounding to the day (use Day instead of Month in the DateDiff and DateAdd), year, hour, whatever.
You can pick a different base date than zero. That one's just convenient on default setups.
It's the fastest, most reliable means I've found for that thus far.
- 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 15, 2011 at 9:47 am
Awesome... this is what I was looking for. Something that was little cleaner than converts and concatenation of dateparts. Seems like it should work well. Thanks.
February 15, 2011 at 10:26 am
You're welcome.
- 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 15, 2011 at 8:01 pm
Since the original example wavered on how to specify the end of the month.....
Do use a construct of "less than the start of next month".
"<= '2/28/2011 23:59:59'" would miss the last second of the day.
"<= '2/28/2011 23:59:59.999'" looks better, but would pick up a row with '3/1/2011 00:00:00.000'
This is due to the resolution of a datetime being less than the three decimals would lead one to believe. At the millisecond level, SQL only records 0,3,and 7 and rounds off other values. 999 gets rounded to the next second.
Run this to see the effect of this rounding:
declare @testtime datetime
set @TestTime = '2/3/2011 23:59:59.990'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.991'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.992'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.993'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.994'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.995'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.996'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.997'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.998'
select @TestTime
set @TestTime = '2/3/2011 23:59:59.999'
select @TestTime
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply