Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DateAdd with a DateTimeoffset sometimes removes the offset

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?

like image 907
Jeremy Avatar asked Oct 20 '25 06:10

Jeremy


1 Answers

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)

like image 111
Code Different Avatar answered Oct 23 '25 01:10

Code Different



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!