In an attempt to remove the time from a DateTimeOffsetValue, I can across a situation where the time zone was being removed - I'm guessing there is an implicit conversion to a DateTime happening here, but why?
DECLARE @d DATETIMEOFFSET = '2013-11-22 00:00:00.000 -07:00';
select
[Original DateTimeOffset] = @d,
[Add 1 Month] = DATEADD(MONTH,1,@d),
[DateAdd and DateDiff] = DATEADD(dd, DATEDIFF(dd, 0, @d), 0);
The above query results in the DateAdd and DateDiff value coming out as a DateTime. I would have thought it would be a DateTimeOffset because the input date is a DateTimeOffset.
Original DateTimeOffset: 2013-11-22 00:00:00.0000000 -07:00
Add 1 Month: 2013-12-22 00:00:00.0000000 -07:00
DateAdd and DateDiff: 2013-11-22 00:00:00.000
Why does that happen?
Because int
is not castable to datetimeoffset
. It boils down to:
[DateAdd and DateDiff] = 0 + 41598 days
How do you interpret that 0? It can't be cast directly to datetimeoffset
:
SELECT CAST(0 as datetimeoffset) -- Error
SELECT CAST(CAST(0 as datetime) as datetimeoffset) -- OK
So SQL Server implicitly cast it to datetime
(i.e. 1990-01-01 00:00:00
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With