Post reply

Date add with Different DataType

  • June 20, 2013 at 8:17 pm

    #294958

    Comments posted to this topic are about the item Date add with Different DataType

  • June 20, 2013 at 10:02 pm

    #1626186

    Good one, thank you for posting.

    To be honest... never came across this message before, so I never tried adding 1 to the DATE type.

    Msg 206, Level 16, State 2, Line 5

    Operand type clash: date is incompatible with int

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • June 20, 2013 at 11:30 pm

    #1626205

    easy one to the end of the week 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • June 20, 2013 at 11:59 pm

    #1626209

    Thanks. Good to know something new.

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • June 21, 2013 at 12:15 am

    #1626211

    Danny Ocean (6/20/2013)

    Thanks. Good to know something new.

    +1 🙂

    ~ 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

  • June 21, 2013 at 1:52 am

    #1626234

    Thanks for the question. Please note though that the 2nd query will also fail on some systems with other language settings.

    SET DATEFORMAT DMY

    GO

    --Query 2

    DECLARE @dt1 DATETIME

    SET @dt1 = '2013-05-30'

    SELECT @dt1+1

    Msg 242, Level 16, State 3, Line 3

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    -----------------------

    NULL

    (1 row(s) affected)

    To find out which languages use this date format, see SELECT alias FROM sys.syslanguages WHERe dateformat = 'DMY'

    German

    French

    Danish

    Spanish

    Italian

    Dutch

    Norwegian

    Portuguese

    Finnish

    Czech

    Polish

    Romanian

    Slovak

    Slovenian

    Greek

    Bulgarian

    Russian

    Turkish

    British English

    Estonian

    Brazilian

    Arabic

    Thai

    Best Regards,

    Chris Büttner

  • June 21, 2013 at 2:17 am

    #1626237

    Another one where the answer only holds if you have YMD Date Format. Most non-US users will get Error, Error here.

    However, it's obvious that the second error is caused by something outside the scope of the question and modifying Query 2 to

    DECLARE @dt1 DATETIME

    SET @dt1 = '2013-30-05'

    SELECT @dt1+1

    will give the desired result for most countries in the world.

  • June 21, 2013 at 2:31 am

    #1626239

    Richard Warr (6/21/2013)

    Another one where the answer only holds if you have YMD Date Format. Most non-US users will get Error, Error here.

    However, it's obvious that the second error is caused by something outside the scope of the question and modifying Query 2 to

    DECLARE @dt1 DATETIME

    SET @dt1 = '2013-30-05'

    SELECT @dt1+1

    will give the desired result for most countries in the world.

    This format is also language specific.

    I always use YYYYMMDD(Un-separated ISO 8601), as it is multilanguage and independent of the dateformat. See section String Literal Date and Time Formats in http://msdn.microsoft.com/en-us/library/ms180878(v=sql.100).aspx

    Best Regards,

    Chris Büttner

  • June 21, 2013 at 2:37 am

    #1626242

    Richard Warr (6/21/2013)it's obvious that the second error is caused by something outside the scope of the question

    obvious after reading the answer anyway.

    luckily I guessed it was yet another US-specific question.

  • June 21, 2013 at 3:02 am

    #1626246

    Though adding an integer to a datetime works (and is even documented HERE and HERE), I would strongly recommend against using it.

    First, let's look at the internals. Operators work with operands of the same data type, and use conversion when needed to ensure this. Since datetime has a higher precedence, the integer value 1 is internally converted to a datetime value (January 2, 1900), and that datetime value is then added to the other operand. So you are actually performing this statement:

    SELECT @dt1+CAST('19000102' AS datetime);

    The only reason that this ends up returning the next day is because of how datetime is internally implemented.

    I don't know about others, but for me, this doesn't give me the warm fuzzy feeling of confidence. 😉

    Second, let's be practical. Microsoft decided not to support implicit conversion from integer for the newer date/time data types. That's why this code fails for the variable declared as date. But it will also fail when the variable is declared as datetime2, or as datetimeoffset. Do you really feel comfortable using code you know will break your database the minute someone decided that the precision if datetime is insufficient and changes the tables and columns to use datetime2 instead?

    Long story short - never use addition (or subtraction) that mixes integer values and datetime (or smalldatetime) values. Always use DATEADD instead.

    And if you're still unconvinced, run the code below and try to make sense of the results.

    SELECT 2000 - getdate();

    EDIT: I guess I should have added my usual comments about using ambiguous date/time formats, but instead I'll just refer the reader to the comments I added to the June 19 QotD.

    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/

  • June 21, 2013 at 3:14 am

    #1626250

    Hugo Kornelis (6/21/2013)

    Though adding an integer to a datetime works (and is even documented HERE and HERE), I would strongly recommend against using it.

    First, let's look at the internals. Operators work with operands of the same data type, and use conversion when needed to ensure this. Since datetime has a higher precedence, the integer value 1 is internally converted to a datetime value (January 2, 1900), and that datetime value is then added to the other operand. So you are actually performing this statement:

    SELECT @dt1+CAST('19000102' AS datetime);

    The only reason that this ends up returning the next day is because of how datetime is internally implemented.

    I don't know about others, but for me, this doesn't give me the warm fuzzy feeling of confidence. 😉

    Second, let's be practical. Microsoft decided not to support implicit conversion from integer for the newer date/time data types. That's why this code fails for the variable declared as date. But it will also fail when the variable is declared as datetime2, or as datetimeoffset. Do you really feel comfortable using code you know will break your database the minute someone decided that the precision if datetime is insufficient and changes the tables and columns to use datetime2 instead?

    Long story short - never use addition (or subtraction) that mixes integer values and datetime (or smalldatetime) values. Always use DATEADD instead.

    And if you're still unconvinced, run the code below and try to make sense of the results.

    SELECT 2000 - getdate();

    EDIT: I guess I should have added my usual comments about using ambiguous date/time formats, but instead I'll just refer the reader to the comments I added to the June 19 QotD.

    Thanks for the wonderful explanation Hugo 🙂

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • June 21, 2013 at 3:49 am

    #1626255

    Hugo Kornelis (6/21/2013)The only reason that this ends up returning the next day is because of how datetime is internally implemented.

    I thought that the main reason it returns the next day, is that the SQL-92 standard says it should?

  • June 21, 2013 at 4:11 am

    #1626257

    Toreador (6/21/2013)

    Hugo Kornelis (6/21/2013)The only reason that this ends up returning the next day is because of how datetime is internally implemented.

    I thought that the main reason it returns the next day, is that the SQL-92 standard says it should?

    Can you provide a source for that? I checked my copy of both the SQL-92 standard and the SQL-2011 standard, and the only thing I was able to find within a reasonable search time was a table outlining valid operators for datetimes and intervals. Adding an interval (not supported in SQL Server 2012) to a date/time value is supported, adding a numeric value, regardless of the exact data type, is not. (And adding two datetime values is not supported either).

    I have attached a screenshot of the relevant table in the SQL2011 standard. The table in SQL-92 is the same (it just looks very ugly on my screen).

    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/

  • June 21, 2013 at 4:34 am

    #1626268

    Hugo Kornelis (6/21/2013)

    Can you provide a source for that?

    yes - my faulty memory 😉

  • June 21, 2013 at 7:36 am

    #1626317

    Great question to end the week and great explanation Hugo. (I always get something from them)

    And thank you for this

    Hugo Kornelis (6/21/2013)

    And if you're still unconvinced, run the code below and try to make sense of the results.

    SELECT 2000 - getdate();

    I can honestly say I've never tried that until now.


    Everything is awesome!

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

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