Finding New Years Eve

Question

Post reply

Finding New Years Eve

Steve Jones - SSC Editor

SSC Guru

Points: 736063
More actions
December 31, 2012 at 9:50 pm

#74167

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

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

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

Jamsheer Ten Centuries Points: 1136 More actions · Answer 1

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

Ganesh Kamal SSChasing Mays Points: 612 More actions · Answer 2

Thanks Steve for the good question on new year.

Happy new year for everyone...

honza.mf SSCertifiable Points: 5519 More actions · Answer 3

Happy New Year

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

Kevin Gill SSCrazy Points: 2348 More actions · Answer 4

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!

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

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/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

(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/

Aaron N. Cutshall SSCrazy Eights Points: 9009 More actions · Answer 8

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!!

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 9

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

TomThomson SSC Guru Points: 104773 More actions · Answer 10

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

sknox SSChampion Points: 12388 More actions · Answer 11

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...?

😛

Lon-860191 SSCrazy Points: 2449 More actions · Answer 12

Lon-860191

SSCrazy

Points: 2449

January 1, 2013 at 8:44 am

#1572695

Regardless, Happy New Year to all.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 13

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

Vera-428803 SSCrazy Points: 2439 More actions · Answer 14

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.