InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 22:36:00 GMT20http://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]DavidBridgeTechnology.com (1/9/2013)[/b][hr]Whilst others are being picky I thought I would add my bit.None of the answers are rightWhen is New Years Eve anyway?If its January 9th (as it is today) then New years eve [u]this year[/u] will be 31-12-2013.31-12-2012 was new years eve [u]LAST YEAR[/u]Very picky I know but I think technically correctDave[/quote]Correct, the original question didn't explicitly state whether it should return New Years Eve for this year or last, but the correct answer implied that it should be last years (2012/12/31)Wed, 09 Jan 2013 09:50:24 GMTnigel.http://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx-- In 2008 + you can use a date instead of a datetime with implicit conversionDECLARE @Date date = GETDATE()SELECT DATEADD(DAY, -DATEPART(dayofyear,@Date),@Date)Wed, 09 Jan 2013 09:34:58 GMTDavidBridgeTechnology.comhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]Toreador (1/9/2013)[/b][hr][quote][b]DavidBridgeTechnology.com (1/9/2013)[/b][hr]When is New Years Eve anyway?[/quote]February 9th ;-)[/quote]Ha Ha.:-PWed, 09 Jan 2013 09:26:42 GMTDavidBridgeTechnology.comhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]DavidBridgeTechnology.com (1/9/2013)[/b][hr]When is New Years Eve anyway?[/quote]February 9th ;-)Wed, 09 Jan 2013 07:42:30 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxWhilst others are being picky I thought I would add my bit.None of the answers are rightWhen is New Years Eve anyway?If its January 9th (as it is today) then New years eve [u]this year[/u] will be 31-12-2013.31-12-2012 was new years eve [u]LAST YEAR[/u]Very picky I know but I think technically correctDaveWed, 09 Jan 2013 07:38:45 GMTDavidBridgeTechnology.comhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]sknox (1/4/2013)[/b][hr][quote][b]nigel. (1/4/2013)[/b][hr]Thanks Steve for the question.Thought I'd throw this in just for fun. Should work for every day of the year:[code="sql"]SELECT DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE())[/code][/quote]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....[/quote]Very good point. How about this one:[code="sql"]SELECT DATEADD(year,DATEDIFF(YEAR,'19001231',GETDATE())-1,'19001231')[/code]Mon, 07 Jan 2013 04:06:36 GMTnigel.http://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]nigel. (1/4/2013)[/b][hr]Thanks Steve for the question.Thought I'd throw this in just for fun. Should work for every day of the year:[code="sql"]SELECT DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE())[/code][/quote]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:[code="sql"]SELECT cast(cast(GETDATE() as int)-DATEPART(dayofyear,getdate())-1 as datetime)[/code]If you have SQL 2008 or higher and want to avoid questionable conversions, you could just modify your code thus:[code="sql"]SELECT CAST(DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE()) as DATE)[/code]*depending on your SQL Server versionFri, 04 Jan 2013 11:32:05 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxOr, since Nigel has led us outside the box....[code="sql"]Select convert(datetime, rtrim(Datepart(year,getdate())-1)+'-12-31')[/code]Yeah, I cheat by using rtrim() as a quickly coded forced conversion to a string.Fri, 04 Jan 2013 09:38:44 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxThanks Steve for the question.Thought I'd throw this in just for fun. Should work for every day of the year:[code="sql"]SELECT DATEADD(DAY, -DATEPART(dayofyear,GETDATE()),GETDATE())[/code]Fri, 04 Jan 2013 05:19:00 GMTnigel.http://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxThanks Steve for the easy points and happy new year to you all!Fri, 04 Jan 2013 01:23:01 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxhappy new year:)Thu, 03 Jan 2013 23:24:07 GMTkalyani.k478http://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]Hugo Kornelis (1/3/2013)[/b][hr]....(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).[/quote]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.Thu, 03 Jan 2013 10:26:06 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]Hugo Kornelis (1/3/2013)[/b][hr]For a method to find "last New Year's Eve" that works correctly in 2013 only, my suggested code would be[code="sql"]SELECT CAST('20121231' AS datetime);[/code][/quote]<Like!>Thu, 03 Jan 2013 03:08:56 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]Steve Jones - SSC Editor (1/1/2013)[/b][hr]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.[/quote]Hi Steve and Happy New Year!We are waiting also for points awarded back for the qotd of 24th december of Hugo Kornelis.Thu, 03 Jan 2013 03:06:59 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]L' Eomot Inversé (1/2/2013)[/b][hr][quote]Argghh! You want it to work next year as well as this???[/quote]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[code="sql"]SELECT CAST('20121231' AS datetime);[/code]Thu, 03 Jan 2013 01:34:43 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]john.arnott (1/2/2013)[/b][hr]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: [code="sql"]SELECT dateadd(month, datediff(month, '19830801', getdate()), '19830731');[/code] 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?[/quote]You are right that any two dates would do (as long as you make sure to use the last day of a [b]31-day[/b] 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.[code="sql"]-- 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 usedDECLARE @StartOfMonth datetime = DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP), '20000101');-- Subtract one day to get last day of previous monthSELECT DATEADD(day, -1, @StartOfMonth);[/code](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).Thu, 03 Jan 2013 01:29:53 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]Hugo Kornelis (1/2/2013)[/b][hr][quote][b]john.arnott (1/2/2013)[/b][hr]Hugo,Just testing to see if we're reading carefully? ;-)Looks as though your option 2 is screwed up somehow, returning dates years off. [code="sql"]SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');[/code]..... returns [code="other"]2008-12-31 00:00:00.000[/code][/quote]Aarrgghh!!! 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:[code="sql"]SELECT dateadd(month, datediff(month, '20110101', getdate()), '20101231');[/code](And Tom's correction is incorrect, though it does incidentally return the last day of the previous month in January 2013 only).[/quote]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).Wed, 02 Jan 2013 20:26:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxThanks, 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: [code="sql"]SELECT dateadd(month, datediff(month, '19830801', getdate()), '19830731');[/code] 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?Wed, 02 Jan 2013 17:45:13 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]john.arnott (1/2/2013)[/b][hr]Hugo,Just testing to see if we're reading carefully? ;-)Looks as though your option 2 is screwed up somehow, returning dates years off. [code="sql"]SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');[/code]..... returns [code="other"]2008-12-31 00:00:00.000[/code][/quote]Aarrgghh!!! 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:[code="sql"]SELECT dateadd(month, datediff(month, '20110101', getdate()), '20101231');[/code](And Tom's correction is incorrect, though it does incidentally return the last day of the previous month in January 2013 only).Wed, 02 Jan 2013 15:48:15 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]john.arnott (1/2/2013)[/b][hr]Hugo,Just testing to see if we're reading carefully? ;-)Looks as though your option 2 is screwed up somehow, returning dates years off. [code="sql"]SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');[/code]..... returns [code="other"]2008-12-31 00:00:00.000[/code][/quote]It's only the constants that are wrong. I think[code]SELECT dateadd(month, datediff(month, getdate(), '20130101'), '20121231');[/code] must be what Hugo meant.edit: Adding 0 months to the date wanted as result is rather neat, isn't it?Wed, 02 Jan 2013 12:07:57 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxHugo,Just testing to see if we're reading carefully? ;-)Looks as though your option 2 is screwed up somehow, returning dates years off. [code="sql"]SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');[/code]..... returns [code="other"]2008-12-31 00:00:00.000[/code]Wed, 02 Jan 2013 11:29:21 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxInteresting one. Happy New Year, Steve!Wed, 02 Jan 2013 09:44:47 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxDoh! I could have sworn this worked for Jan 30. I must have been celebrating still when I looked at it yesterday.Corrected and points awarded back.Wed, 02 Jan 2013 09:08:33 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]Toreador (1/2/2013)[/b][hr][quote][b]Steve Jones - SSC Editor (1/1/2013)[/b][hr]I changed to question to exclude Jan 31, this that doesn't work.[/quote]Jan 30 won't work either. Jan 29 will only work in leap years.[/quote]+1Wed, 02 Jan 2013 07:46:42 GMTsestell1http://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]Steve Jones - SSC Editor (1/1/2013)[/b][hr]I changed to question to exclude Jan 31, this that doesn't work.[/quote]Jan 30 won't work either. Jan 29 will only work in leap years.Wed, 02 Jan 2013 02:56:19 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxHappy new year to all SQL serverCentral guyz..Wed, 02 Jan 2013 02:52:09 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxNice one..Happy New Year to all of you......Wed, 02 Jan 2013 00:42:53 GMTBanglahttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxThanks Steve.. Great Start of the Year... Happy New Year to you and your team....Wed, 02 Jan 2013 00:28:37 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxHappy New Year to everyone!Wed, 02 Jan 2013 00:03:03 GMTgserdijnhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxThanks Steve. Wish you a very happy new year.Tue, 01 Jan 2013 23:39:48 GMTbaabhuhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxThanks SteveTue, 01 Jan 2013 23:14:53 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxI think this method with the nested DATEADD/DATEDIFF is simple and has the advantage of eliminating the time part.[code="sql"]select a.DT, [LastDayOfPriorMonth] = dateadd(mm,datediff(mm,-1,a.DT)-1,-1), [LastDayOfPriorYear] = dateadd(yy,datediff(yy,-1,a.DT)-1,-1)from ( -- Test Dates select top 32 DT = getdate()-1+ row_number() over (order by object_id) from sys.objects ) aorder by a.DT[/code]Results:[code="plain"]DT LastDayOfPriorMonth LastDayOfPriorYear----------------------- ----------------------- -----------------------2013-01-01 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-02 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-03 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-04 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-05 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-06 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-07 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-08 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-09 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-10 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-11 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-12 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-13 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-14 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-15 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-16 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-17 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-18 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-19 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-20 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-21 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-22 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-23 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-24 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-25 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-26 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-27 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-28 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-29 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-30 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-01-31 20:47:27.733 2012-12-31 00:00:00.000 2012-12-31 00:00:00.0002013-02-01 20:47:27.733 2013-01-31 00:00:00.000 2012-12-31 00:00:00.000[/code]Tue, 01 Jan 2013 18:50:29 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxParens 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.Tue, 01 Jan 2013 17:11:33 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxHappy New Year :-)concerning the QoTD: none of the syntax will work because of missing parenthesis. But hey, it's a good laugh and that's what this catergory is about.Tue, 01 Jan 2013 13:03:00 GMTVera-428803http://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxYou've all missed the biggest error - besides the parenthesis and February issues. This question is listed in category "humor", and isn't one bit funny! :-)Happy New Year!Tue, 01 Jan 2013 08:52:52 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxRegardless, Happy New Year to all.Tue, 01 Jan 2013 08:44:50 GMTLon-860191http://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspx[quote][b]L' Eomot Inversé (1/1/2013)[/b][hr]That's quite a mess, isn't it!The only correct answer is "none of the above", for two reasons:1) none of the select statements is syntactically valid.2) even when the missing parentheses are added to make them syntactically valid, none of them will return 31.12.2012 on 31st Jan 2013 because February doesn't have 31 days this year.If you want a select statement that does what is sepcified, the simple approach is[code]select dateadd(day,-datepart(day,GETDATE()),GETDATE())[/code]edit: I see Hugo posted a better method with only one getdate() call and returning a date without any time.[/quote]Came to post this same thing, and saw that you and Hugo had already caught it. Darn you time zones!!![quote]...because February doesn't have 31 days this year.[/quote]In Steve's defense, perhaps this code was originally written in a year where February [i]did[/i] have 31 days...?:-PTue, 01 Jan 2013 08:40:08 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxThat's quite a mess, isn't it!The only correct answer is "none of the above", for two reasons:1) none of the select statements is syntactically valid.2) even when the missing parentheses are added to make them syntactically valid, none of them will return 31.12.2012 on 31st Jan 2013 because February doesn't have 31 days this year.If you want a select statement that does what is sepcified, the simple approach is[code]select dateadd(day,-datepart(day,GETDATE()),GETDATE())[/code]edit: I see Hugo posted a better method with only one getdate() call and returning a date without any time.Tue, 01 Jan 2013 08:08:20 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxis it me the only one who finds all answers wrong as there's a missing parenthesis in all of them??depends on where you place the missing one in 'select 2' you get the right answer or syntax error which is kinda common tricky answer for a QotD...Tue, 01 Jan 2013 07:45:46 GMTraulggonzalezhttp://www.sqlservercentral.com/Forums/Topic1401545-32-1.aspxI also noted the missing parentheses as well. I believe that adding a month before getting the day was simply a red herring to throw you off, but as stated previously it won't work for the entire month of January because February doesn't have 31 days in it! :-P In any case, it was a good thought exercise!!Tue, 01 Jan 2013 05:17:10 GMTAaron N. Cutshall