4

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?

Jeremy
  • 44,950
  • 68
  • 206
  • 332
  • 1
    I am guessing when you did a datediff it removed the timezone as it returns an int and then it got converted to a datetime. – Sam Jun 17 '16 at 18:19
  • I think you're right. So then is there a better way to get the date (without the time) but with the offset? I ended up with an ugly calculation: TODATETIMEOFFSET(DATEADD(dd, DATEDIFF(dd, 0, DATE_CREATED), 0), DATEPART(tz,SYSDATETIMEOFFSET())) – Jeremy Jun 17 '16 at 19:36
  • I know this is old, but why does the offset matter if you are stripping off the time anyways? A offset is to calculate the correct time for a corresponding time zone. The offset does not effect the date unless there is a time present. – John C Nov 12 '18 at 18:27
  • @JohnC - I'm not really stripping the time off, I'm basically rounding down to midnight. – Jeremy Nov 20 '18 at 17:17
  • But if you're removing all time down to midnight, then the timezone won't matter for any of them. – John C Nov 20 '18 at 17:35
  • I don't think that is true. midnight in one timezone is not midnight in another timezone – Jeremy Nov 20 '18 at 18:29

1 Answers1

3

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)

Code Different
  • 90,614
  • 16
  • 144
  • 163
  • 3
    Thanks. I ended up wrapping it in a TODATETIMEOFFSET function: TODATETIMEOFFSET(DATEADD(dd, DATEDIFF(dd, 0, DATE_CREATED), 0), DATEPART(tz,SYSDATETIMEOFFSET())) – Jeremy Jun 17 '16 at 19:34