Data Type Precedence

  • Avi1

    SSCrazy

    Points: 2313

    Comments posted to this topic are about the item Data Type Precedence

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71452

    Nice one, thanks Avinash

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Rune Bivrin

    SSCertifiable

    Points: 7593

    Nice one. I got it wrong, simply because I figured adding two dates just doesn't make any sense, and so the result should be the strings concatenated.


    Just because you're right doesn't mean everybody else is wrong.

  • Carlo Romagnano

    SSC-Insane

    Points: 21811

    Rune Bivrin - Thursday, October 5, 2017 1:05 AM

    Nice one. I got it wrong, simply because I figured adding two dates just doesn't make any sense, and so the result should be the strings concatenated.

    That's for me, too!

  • edwardwill

    SSCarpal Tunnel

    Points: 4983

    Good question, thanks.

  • dale_berta

    SSC Eights!

    Points: 922

    I figured precedence should cast @txt to some form of datetime. But I didn't like any of the answers, because I didn't think you could add two datetimes together. The reference (https://technet.microsoft.com/en-us/library/ms178565(v=sql.105).aspx) says any of the numeric types except bit, and that it cannot be used with date, time, datetime2, or datetimeoffset. It doesn't specifically mention datetime.

    You can add a number to a datetime, which adds that number of days, but adding two datetimes doesn't make sense. I'm going to postulate that  the expression evaluator is converting one of the datetimes to the integer which represents its number-of-days-since-the beginning-of-time, and then adding that integer to the other datetime. Which seems about right if you actually run the code and try to figure out the result you get.

  • Luis Cazares

    SSC Guru

    Points: 183576

    For those who don't understand the addition of dates.
    Datetime is stored internally as 2 integers: one for the date and one for the time. Each date has an integer value equivalent, so basically, those integers are adding internally while keeping the datetime data type.
    This only works for datetime and smalldatetime. The functionality was removed for the new date/time data types: datetime2, date, time and datetimeoffset.

    P.S. It also works if you try to substract one date from another.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Budd

    Hall of Fame

    Points: 3656

    ** LIGHT BULB **
    I agree that adding 2 dates makes no sense, but what I was puzzled by was how it came back with an increase of 101 days.  I started changing the dates and then it dawned on me, 1900.01.01 default start date.

  • Luis Cazares

    SSC Guru

    Points: 183576

    dale_berta - Thursday, October 5, 2017 5:33 AM

    I'm going to postulate that  the expression evaluator is converting one of the datetimes to the integer which represents its number-of-days-since-the beginning-of-time, and then adding that integer to the other datetime. Which seems about right if you actually run the code and try to figure out the result you get.

    That's close but not accurate. Date zero is 1900-01-01, but the lower limit is 1753-01-01 probably due to Julian/Gregorian calendars differences.
    You can try casting integers to datetimes or datetimes to integers to prove this.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • SQL Surfer '66

    SSCertifiable

    Points: 5152

    SELECT result = @dt + @txt

    INTO t;

    EXEC sp_help t

    Using sp_help to see the metadata. Brilliant tip. I'll gonna use this a lot. Now I've only used it for like procedures.

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

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