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.
Rune Bivrin - Thursday, October 5, 2017 1:05 AM
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.
Hall of Fame
** 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
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.
SELECT result = @dt + @txt
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)