Finding New Years Eve

  • Comments posted to this topic are about the item Finding New Years Eve

  • Thanks Steve for the new year gift. And Happy new year for entire "sqlservercenter" family..

  • Thanks Steve for the good question on new year.

    Happy new year for everyone...

  • Happy New Year



    See, understand, learn, try, use efficient
    Β© Dr.Plch

  • Just to be picky, it actually won't work after the 28th Jan due to that dateadd of a month in there πŸ˜›

    Happy new year

    Kev

    -------------------------------Oh no!

  • Happy New Year to all πŸ™‚

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Kevin Gill (1/1/2013)


    Just to be picky, it actually won't work after the 28th Jan due to that dateadd of a month in there πŸ˜›

    And to be even more picky, all three answer options have a mismatch in the parentheses. Here is the corrected version:

    -- select 1

    SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),dateadd(mm,-1,GETDATE()))

    -- select 2

    SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),GETDATE())

    -- select 3

    SELECT dateadd(day,-(datepart(day,dateadd(mm,1,GETDATE()))),dateadd(mm,1,GETDATE()))

    And for those who prefer to use efficient code to get the last day of the previous month, use either one of these options:

    -- Option 1

    SELECT dateadd(day, -datepart(day, getdate()), getdate());

    -- Option 2

    SELECT dateadd(month, datediff(month, getdate(), '20110101'), '20101231');

    Option 2 has the additional advantage that you get only the day; all other options include a time part.

    (None of this is intended as criticism of a question, just to provide some additional information).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • (deleted)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I 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! πŸ˜› In any case, it was a good thought exercise!!

  • is 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...

  • 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

    select dateadd(day,-datepart(day,GETDATE()),GETDATE())

    edit: I see Hugo posted a better method with only one getdate() call and returning a date without any time.

    Tom

  • L' Eomot InversΓ© (1/1/2013)


    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

    select dateadd(day,-datepart(day,GETDATE()),GETDATE())

    edit: I see Hugo posted a better method with only one getdate() call and returning a date without any time.

    Came to post this same thing, and saw that you and Hugo had already caught it. Darn you time zones!!!

    ...because February doesn't have 31 days this year.

    In Steve's defense, perhaps this code was originally written in a year where February did have 31 days...?

    πŸ˜›

  • Regardless, Happy New Year to all.

  • You'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!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Happy 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.

Viewing 15 posts - 1 through 15 (of 47 total)

You must be logged in to reply to this topic. Login to reply