January 2, 2013 at 5:45 pm
Thanks, Hugo, for the correction. I like to think, however, that you'd agree that Michael V Jones's code above would be a bit more clear. Your two literal dates aren't particularly special; they are simply the 31st of a month and the 1st of the next. Thus using a mid-year date from thirty years ago as a starting point works as well: SELECT dateadd(month, datediff(month, '19830801', getdate()), '19830731'); Of course, I'd not want to see this as is in production code, but since we're in QOTD land, a bit of obfuscation is valid, right?
January 2, 2013 at 8:26 pm
Hugo Kornelis (1/2/2013)
john.arnott (1/2/2013)
Hugo,Just testing to see if we're reading carefully? 😉
Looks as though your option 2 is screwed up somehow, returning dates years off.
SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');..... returns
2008-12-31 00:00:00.000Aarrgghh!!! I know I have the habit of reversing the two arguments to the DATEDIFF function, which is why I *almost* always test it before posting. *almost* :Whistling:
Here is the correct code:
SELECT dateadd(month, datediff(month, '20110101', getdate()), '20101231');(And Tom's correction is incorrect, though it does incidentally return the last day of the previous month in January 2013 only).
Argghh! You want it to work next year as well as this???
I never thought of that!
I suppose I should have - the question doesn't say "this January".
But it does say "January" - but I won't waste time on finding a version that works only in January (although that would be easy enough) so I could claim that that was a better answer than yours (because it wouldn't be).
Tom
January 3, 2013 at 1:29 am
john.arnott (1/2/2013)
Thanks, Hugo, for the correction. I like to think, however, that you'd agree that Michael V Jones's code above would be a bit more clear. Your two literal dates aren't particularly special; they are simply the 31st of a month and the 1st of the next. Thus using a mid-year date from thirty years ago as a starting point works as well:SELECT dateadd(month, datediff(month, '19830801', getdate()), '19830731');Of course, I'd not want to see this as is in production code, but since we're in QOTD land, a bit of obfuscation is valid, right?
You are right that any two dates would do (as long as you make sure to use the last day of a 31-day month and the next day, otherwise your code will be buggy). When posting this solution, I often deliberately use a date a few years back, to emphasize this point. If I would use the current date in my example, some readers would think they need to change the code every year.
I do not agree that Michael V Jones's code is more clear. It uses an integer as an argument to a function that accepts date or datetime data only, so it relies on implicit conversion, in this case from int to datetime (the integer value -1 will convert to the datetime value 1899-12-31T0:00:00, and the reason Michael's code works is that this is the 31st day of a month).
If you want easier to understand code, I would take a two-step approach - first truncating to the first of the month, then subtracting a day.
-- First step: Truncate current date to the start of the month
-- The date '20000101' below is a base date; any other first of a month can be used
DECLARE @StartOfMonth datetime = DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP), '20000101');
-- Subtract one day to get last day of previous month
SELECT DATEADD(day, -1, @StartOfMonth);
(And yes, 20000101 is yet another more or less randomly chosen base date. I used to use 19000101 here -it seemed appropriate to use the base date-, but stopped doing that when I wanted to use the same technique to truncate to other time units, such as the last full day, year, minute, or second - when using this technique with seconds, you get an integer overflow, and using a more recent base date avoids that).
January 3, 2013 at 1:34 am
L' Eomot Inversé (1/2/2013)
Argghh! You want it to work next year as well as this???
I had in fact broadened the topic to describing methods I would suggest for finding the last day of the previous month at any time in any year.
For a method to find "last New Year's Eve" that works correctly in 2013 only, my suggested code would be
SELECT CAST('20121231' AS datetime);
January 3, 2013 at 3:06 am
Steve Jones - SSC Editor (1/1/2013)
Parens corrected, and my apologies. I changed to question to exclude Jan 31, this that doesn't work.Happy New Year.
I'll award back points tomorrow.
Hi Steve and Happy New Year!
We are waiting also for points awarded back for the qotd of 24th december of Hugo Kornelis.
January 3, 2013 at 3:08 am
Hugo Kornelis (1/3/2013)
For a method to find "last New Year's Eve" that works correctly in 2013 only, my suggested code would be
SELECT CAST('20121231' AS datetime);
<Like!>
January 3, 2013 at 10:26 am
Hugo Kornelis (1/3/2013)
....(And yes, 20000101 is yet another more or less randomly chosen base date. I used to use 19000101 here -it seemed appropriate to use the base date-, but stopped doing that when I wanted to use the same technique to truncate to other time units, such as the last full day, year, minute, or second - when using this technique with seconds, you get an integer overflow, and using a more recent base date avoids that).
Hugo,
Thanks for opening my eyes. Michael's code looked very clear to me, but I realize now that it's only because it followed a familiar pattern, just using -1 rather than a 0 as a base date for the DATEADD()/DATEDIFF() conversion, and not because it truly is clear. Using something obviously recognizable as a date is an improvement. And thanks again for your parenthetical about using '20000101' instead of '19000101' or 0. I'd actually seen that overflow when I got down to seconds while playing around with the technique, and thought "Oh, well. Doesn't work there." Of course, now I have to go back and put that into my working-with-dates cheat sheet of code snippets.
January 3, 2013 at 11:24 pm
happy new year:)
January 4, 2013 at 1:23 am
Thanks Steve for the easy points and happy new year to you all!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 4, 2013 at 5:19 am
Thanks Steve for the question.
Thought I'd throw this in just for fun. Should work for every day of the year:
SELECT DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE())
January 4, 2013 at 9:38 am
Or, since Nigel has led us outside the box....
Select convert(datetime, rtrim(Datepart(year,getdate())-1)+'-12-31')
Yeah, I cheat by using rtrim() as a quickly coded forced conversion to a string.
January 4, 2013 at 11:32 am
nigel. (1/4/2013)
Thanks Steve for the question.Thought I'd throw this in just for fun. Should work for every day of the year:
SELECT DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE())
The only problem with that is, since getdate() returns a datetime[2]*, you're actually returning some time during the day on New Years Eve rather than the day of New Years Eve itself.
Of course, as of SQL2008R2, this still works:
SELECT cast(cast(GETDATE() as int)-DATEPART(dayofyear,getdate())-1 as datetime)
If you have SQL 2008 or higher and want to avoid questionable conversions, you could just modify your code thus:
SELECT CAST(DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE()) as DATE)
*depending on your SQL Server version
January 7, 2013 at 4:06 am
sknox (1/4/2013)
nigel. (1/4/2013)
Thanks Steve for the question.Thought I'd throw this in just for fun. Should work for every day of the year:
SELECT DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE())The only problem with that is, since getdate() returns a datetime[2]*, you're actually returning some time during the day on New Years Eve rather than the day of New Years Eve itself.
...
Very good point. How about this one:
SELECT DATEADD(year,DATEDIFF(YEAR,'19001231',GETDATE())-1,'19001231')
January 9, 2013 at 7:38 am
Whilst others are being picky I thought I would add my bit.
None of the answers are right
When is New Years Eve anyway?
If its January 9th (as it is today) then New years eve this year will be 31-12-2013.
31-12-2012 was new years eve LAST YEAR
Very picky I know but I think technically correct
Dave
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
January 9, 2013 at 7:42 am
DavidBridgeTechnology.com (1/9/2013)
When is New Years Eve anyway?
February 9th 😉
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply