Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values

  • Comments posted to this topic are about the item Stairway to Advanced T-SQL Level 9: Compare, Modify, Derive and Validate Date and Time Values

    Gregory A. Larsen, MVP

  • I have found it more important to understand how the tools my users use interpret date and time fields. Some older but widely used tools don't work well with Date and Time fields and smalldatetime or DateTime is the only field type they can dependably use.

  • Good article. I didn't realize that there was an EOMONTH function. I'm on 2008 R2 and could use that function, a lot.

  • I have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.

    I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".

  • 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

  • Joseph M. Steinbrunner (11/25/2015)


    ...did you notice that the EOMONTH returns a DATE instead of what the tooltip reads (DATETIME)?

    Good point. Microsoft states the Return Type as DATE (https://msdn.microsoft.com/en-us/library/hh213020.aspx) but indeed, my SQL 2012 Intellisense says "Returns DATETIME".

  • Can you please elaborate onQ1?  Based on the chart at the top, the shortest a DATETIMEOFFSET can be is 8 and the longest aDATETIME2 can be is 8 so “AT BEST” they are equal.

  • thisisfutile - Wednesday, November 25, 2015 7:49 AM

    I have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".

    I realize this question is 2 years old but thought I'd provide my thoughts on it.  It's either because MS was in a hurry to release the new datatypes and didn't understand the value of direct date math or... hmmm... nah.... that's gotta be it. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just a couple of personal notes not covered in the article...

    I certainly can't speak for anyone else but I find the ISDATE() function to have the same problems as the ISNUMERIC() function.  For example, SELECT ISNUMERIC(',')  will return a "1" because SELECT CONVERT(MONEY,',')  will return a "0.00".  For a similar reason, SELECT ISDATE('JAN1753') will return a "1" even it was really meant to be a user login (no spaces) for someone.  Another example is (even though DATE can handle it),  SELECT ISDATE('1753-01-01') will correctly return a "1" but  will incorrectly SELECT ISDATE('1752-01-01') return a "0"... or so it would seem until you read the fine print.  It only works with strings that can be converted to a DATETIME data type.

    Don't get me wrong... I don't want anyone at MS to make a change there.  It just takes a little knowledge as to what it can and cannot do.

    As for EOMONTH, I avoid it like the plague even for DATE data types.  You just don't know when someone is going to change the data type of a column to make it more resolute.  I always use the form (for months in this example) of  WHERE someDTcolumn >= {first of month} AND someDTcolumn < {first of NEXT month} and it has saved my hinny many times.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Sunday, April 2, 2017 7:20 PM

    thisisfutile - Wednesday, November 25, 2015 7:49 AM

    I have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".

    I realize this question is 2 years old but thought I'd provide my thoughts on it.  It's either because MS was in a hurry to release the new datatypes and didn't understand the value of direct date math or... hmmm... nah.... that's gotta be it. 😉

    I literally, laughed out loud reading this.  I'm glad I'm not the only one who felt it was simply oversight.

  • thisisfutile - Wednesday, April 12, 2017 3:28 PM

    Jeff Moden - Sunday, April 2, 2017 7:20 PM

    thisisfutile - Wednesday, November 25, 2015 7:49 AM

    I have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".

    I realize this question is 2 years old but thought I'd provide my thoughts on it.  It's either because MS was in a hurry to release the new datatypes and didn't understand the value of direct date math or... hmmm... nah.... that's gotta be it. 😉

    I literally, laughed out loud reading this.  I'm glad I'm not the only one who felt it was simply oversight.

    Oooooo... I don't think it was an oversight... I think it was a bit of ignorance. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Wednesday, April 12, 2017 3:53 PM

    thisisfutile - Wednesday, April 12, 2017 3:28 PM

    Jeff Moden - Sunday, April 2, 2017 7:20 PM

    thisisfutile - Wednesday, November 25, 2015 7:49 AM

    I have a question for the community. What's the reasoning that DATE fields can't simply have a number added to them without the use of the DATEADD function? I assume it comes down to implicit conversions? This arithmetic always was (and still is) possible with DATETIME fields.I'm a late comer to SQL 2008 and later versions, so my introduction to DATE and TIME occurred recently. I was quite surprised to see all of my old scripts fail when I simply wanted to add a +1 to a DATE field and received "Operand type clash: date is incompatible with int". I then discovered that DATEADD was necessary. To me, it's much easier to read and type "MyDateField + 1".

    I realize this question is 2 years old but thought I'd provide my thoughts on it.  It's either because MS was in a hurry to release the new datatypes and didn't understand the value of direct date math or... hmmm... nah.... that's gotta be it. 😉

    I literally, laughed out loud reading this.  I'm glad I'm not the only one who felt it was simply oversight.

    Oooooo... I don't think it was an oversight... I think it was a bit of ignorance. 😉

    :laugh:

  • I created this list when I want to remember the formats:

    select 100 'Convert_style',convert(varchar,GETDATE(),100) 'Output', 'MMM dd yyyy hh:mm/M' 'format', 'datetime' 'Type' union
    select 101 'Convert_style',convert(varchar,GETDATE(),101) 'Output', 'mm/dd/yyyy' 'format', 'date' 'Type' union
    select 102,convert(varchar,GETDATE(),102), 'yyyy.mm.dd', 'date' union
    select 103,convert(varchar,GETDATE(),103), 'dd/mm/yyyy', 'date' union
    select 104,convert(varchar,GETDATE(),104), 'dd.mm.yyyy', 'date' union
    select 105,convert(varchar,GETDATE(),105), 'dd-mm-yyyy', 'date' union
    select 106,convert(varchar,GETDATE(),106), 'dd MMM yyyy', 'date' union
    select 107,convert(varchar,GETDATE(),107), 'MMM dd, yyyy', 'date' union
    select 108,convert(varchar,GETDATE(),108), 'HH:mm:ss', 'time' union -- NN = minutes
    select 109,convert(varchar,GETDATE(),109), 'MMM dd yyyy h:mm:ss:000/M ', 'datetime' union
    select 110,convert(varchar,GETDATE(),110), 'mm-dd-yyyy', 'date' union
    select 111,convert(varchar,GETDATE(),111), 'yyyy/mm/dd', 'date' union
    select 112,convert(varchar,GETDATE(),112), 'yyyymmdd', 'date' union
    select 113,convert(varchar,GETDATE(),113), 'dd MMM yyyy HH:mm:ss.000', 'datetime' union
    select 114,convert(varchar,GETDATE(),114), 'hh:mm:ss.hhh', 'time' union
    select 120,convert(varchar,GETDATE(),120), 'yyyy-mm-dd HH:mm:ss', 'datetime' union
    select 121,convert(varchar,GETDATE(),121), 'yyyy-mm-dd HH:mm:ss.000', 'datetime' union

    select 126,convert(varchar,GETDATE(),126),'yyyy-mm-ddTHH:mm:ss.000','datetime' union
    select 127,convert(varchar,GETDATE(),127),'yyyy-mm-ddTHH:mm:ss.000','datetime' union
    select 130,convert(varchar,GETDATE(),130),'different date + hh:mm:ss:00','datetime' union
    select 131,convert(varchar,GETDATE(),131),'different date + hh:mm:ss:000/M','datetime'

    412-977-3526 call/text

  • Jeff Moden - Sunday, April 2, 2017 7:49 PM

    Just a couple of personal notes not covered in the article...

    I certainly can't speak for anyone else but I find the ISDATE() function to have the same problems as the ISNUMERIC() function.  For example, SELECT ISNUMERIC(',')  will return a "1" because SELECT CONVERT(MONEY,',')  will return a "0.00".  For a similar reason, SELECT ISDATE('JAN1753') will return a "1" even it was really meant to be a user login (no spaces) for someone.  Another example is (even though DATE can handle it),  SELECT ISDATE('1753-01-01') will correctly return a "1" but  will incorrectly SELECT ISDATE('1752-01-01') return a "0"... or so it would seem until you read the fine print.  It only works with strings that can be converted to a DATETIME data type.

    Don't get me wrong... I don't want anyone at MS to make a change there.  It just takes a little knowledge as to what it can and cannot do.

    As for EOMONTH, I avoid it like the plague even for DATE data types.  You just don't know when someone is going to change the data type of a column to make it more resolute.  I always use the form (for months in this example) of  WHERE someDTcolumn >= {first of month} AND someDTcolumn < {first of NEXT month} and it has saved my hinny many times.

    Thanks for pointing out problems with isDate and isNumeric.  I no longer use either.  When I first encountered isDate, I naturally thought that a string that returned a 1 could be cast as a date.  As you point out that is not the case.  Equally troubling is isDate('2010') that returns a 1 and does cast to date(1/1/2010).  A CLR is a better solution when date validation is required.

  • billh-508166 - Wednesday, September 27, 2017 1:04 PM

    A CLR is a better solution when date validation is required.

    Why would you think that?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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