Data Type Precedence

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

  • Nice one, thanks Avinash

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

  • 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.

  • 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!

  • Good question, thanks.

  • 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.

  • 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
  • ** 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.

  • 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
  • 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 9 (of 9 total)

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