Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Gregory Larsen Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values RE: Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values

  • November 25, 2015 at 7:50 am

    #1842317

    Thanks for the good article.

    Just as a comment (not on your article specifically), did you notice that the EOMONTH returns a DATE instead of what the tooltip reads (DATETIME)? If you run the following to get the last accessible nanosecond (DATETIME type) for the end of the month (my typical report queries), you get an error.

    SELECT DATEADD(ms,-3,DATEADD(dd,1,EOMONTH(GETDATE())))

    Msg 9810, Level 16, State 1, Line 1

    The datepart millisecond is not supported by date function dateadd for data type date.

    You actually have to convert it to a DATETIME to get the value.

    SELECT DATEADD(ms,-3,DATEADD(dd,1,CONVERT(DATETIME,EOMONTH(GETDATE()))))

    2015-11-30 23:59:59.997