New Years Eve, is that still Dec. 31 this year?



  • I came across an article [ http://www.databasejournal.com/features/mssql/article.php/3076421 ] that suggests using this lovely WTF-ish code in MS SQL Server to calculate the "Last Day of Current Year":

    <FONT face="Courier New">select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))</FONT>

    Am I missing something here? Why in the world would you want to use the code above instead of the following?

    <FONT face="Courier New">select cast( cast(year(getdate()) as char(4) + '-12-31' as datetime )</FONT>

     



  • Actually, the DATEADD/DATEDIFF combo has a couple of advantages: it doesn't need to cast to another type [which if using a datetime column from a large set of data and not getdate() can have performance implications], and you can simply plug in another datepart parameter x and get the end of x, e.g.

    <FONT face="Courier New">select dateadd(ms,-3,DATEADD(month, DATEDIFF(month,0,getdate()  )+1, 0))</FONT>

    giving an easy to remember pattern. With a character type casting, you have to have a different formula for each date part.

    I haven't found a use, though, for the millisecond part of this - typically, this is done to give a BETWEEN for dates (e.g., using constants <FONT face="Courier New">where datecolumn between '</FONT><FONT face="Courier New">2006-01-01' and '2006-12-31 23:59:59.997'<FONT face=Tahoma>). It's better to just use </FONT></FONT><FONT face="Courier New">where datecolumn>='2006-01-01' and datecolumn<'2007-01-01'
    </FONT>



  • In order to be fair we have to take into account the message at the start of the explanations:

    "Remember now, this example and all the other examples in this article will only be using the DATEADD and DATEDIFF functions to calculate our desired date"

    so we can take it more like an exercise than something to put in our sp.

    This will be 100 times better if it were in the middle of a dinamic sql query -> then I will love it (no parameters, of course, just the "inject sql here" kind of query you often saw at websites)



  • @A Wizard A True Star said:

    I came across an article [ http://www.databasejournal.com/features/mssql/article.php/3076421 ] that suggests using this lovely WTF-ish code in MS SQL Server to calculate the "Last Day of Current Year":

    <font face="Courier New">select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()  )+1, 0))</font>

    Am I missing something here? Why in the world would you want to use the code above instead of the following?

    <font face="Courier New">select cast( cast(year(getdate()) as char(4) + '-12-31' as datetime )</font>

     



    did you ever think about the fact that some people have different locales?

    we would write that as 31/12/thisyear, thats format 101 in sql server or something, i would have to check in books online


  • @t-bone said:

    @A Wizard A True Star said:

    <font face="Courier New">select cast( cast(year(getdate()) as char(4) + '-12-31' as datetime )</font>



    did you ever think about the fact that some people have different locales?

    we would write that as 31/12/thisyear, thats format 101 in sql server or something, i would have to check in books online




    did you ever think about googling for ISO 8601? Then you might learn
    that yyyy-mm-dd was chosen exactly to avoid confusions between
    different locale specific date formats. Please enlighten me on which
    locales that interprets 2006-01-12 as anything different than 12th of
    January 2006.



Log in to reply