Date add with Different DataType

  • paresh-1036946

    Mr or Mrs. 500

    Points: 570

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

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • kapil_kk

    SSC-Insane

    Points: 21316

    easy one to the end of the week ๐Ÿ™‚

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

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Thanks. Good to know something new.

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

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    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

  • Richard Warr

    SSCertifiable

    Points: 6957

    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.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    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

  • Toreador

    SSChampion

    Points: 11257

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Kingston Dhasian

    SSCoach

    Points: 19794

    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/

  • Toreador

    SSChampion

    Points: 11257

    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?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Toreador

    SSChampion

    Points: 11257

    Hugo Kornelis (6/21/2013)


    Can you provide a source for that?

    yes - my faulty memory ๐Ÿ˜‰

  • Dana Medley

    SSCertifiable

    Points: 6764

    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 22 total)

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