2

I am trying to convert a string into DateTimeOffset (in SQL Server) through a ETL job. Basically, my string would look something like '2017-10-15' and I want this to be converted into a DatetimeOffset (from the current DB server). 

SELECT
    SWITCHOFFSET(DATEADD(mi, DATEDIFF(MI, GETDATE(), GETUTCDATE()), CAST(@DateInString  + ' 00:00:00' AS DATETIMEOFFSET)), DATENAME(tzoffset, SYSDATETIMEOFFSET()))

I have been getting some weird issues with this statement as the final output would fall either +1 / -1 minute than the expected ones. This happens for at least every 10 records/million. I tried to nail down the issue and I could see the problem was with the DATEDIFF() method returning +/-1 minute.

SELECT DATEDIFF(MI, GETDATE(), GETUTCDATE())

This should exactly return -600 (since my DB server UTC is +10). However, it returns either -599 or 601 for few records. I execute them as a single select statement in my Stored Procedure and return it as a parameter.

This is weird on how SQL could detect two different datetime values for GETDATE() and GETUTCDATE() on the same select statement.

Is there a way to force SQL to get exactly same dates in those DATEDIFF parameters or am I missing something here? Thanks in advance

I am using SQL Server 2014 (v12.0).

Stored procedure:

CREATE PROCEDURE dbo.SPConvertDateTimeOffset
    @DateInString VARCHAR(10),
    @DateTimeOffset_Value DATETIMEOFFSET OUTPUT,
    @Datediff_Value INT OUTPUT
AS 
BEGIN 
    -- This line returns +/- 1
    SELECT @Datediff_Value = DATEDIFF(MI, GETDATE(), GETUTCDATE())  

    SELECT @DateTimeOffset_Value = SWITCHOFFSET(DATEADD(mi, @Datediff_Value, CAST(@DateInString  + ' 00:00:00' AS DATETIMEOFFSET)), DATENAME(tzoffset, SYSDATETIMEOFFSET()))
END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bharathi
  • 1,015
  • 13
  • 41
  • If you do this many, many times, then `getdate()` and `getutcdate()` might be off by a few milliseconds. – Gordon Linoff Oct 16 '17 at 01:24
  • Does it really matter? Because, I get the datediff only for Minutes part. Even if I do it several times, ideally it should get me the same datetime value. However, do you have any reference on why it have difference in millisecs if I do it several times? – Bharathi Oct 16 '17 at 01:28
  • 3
    It can matter because `datediff()` measures *boundaries* between times. – Gordon Linoff Oct 16 '17 at 01:44

2 Answers2

0

The two functions are not executed simultaneously. So about 1 time in 100,000 (in your test) the times are on opposite sides of a minute boundary.

If you just want the timezone, you could try:

select datepart(tz, SYSDATETIMEOFFSET())

Rupert Morrish
  • 785
  • 2
  • 10
  • 18
0

@GordonLinoff has explained why this happens: since functions are executed at slightly different times, they may return a different minute.

To work around, try this:

DECLARE @DateTimeOffset_Value Datetimeoffset
DECLARE @Datediff_Value INT, @DateInString VARCHAR( 10 )
SET @DateInString = CONVERT( VARCHAR, GETDATE(), 112 )
SET @DateTimeOffset_Value = TODATETIMEOFFSET( @DateInString, DATENAME(tzoffset,SYSDATETIMEOFFSET()))
SET @Datediff_Value = DATEDIFF( MI, @DateInString, @DateTimeOffset_Value )
SELECT @DateInString, @DateTimeOffset_Value, @Datediff_Value

It does not use current date comparisons.

Note: that during the time when day light saving changes you may get a different value from the expected, depending on when exactly the code was run.

Have a look at https://dba.stackexchange.com/questions/28187/how-can-i-get-the-correct-offset-between-utc-and-local-times-for-a-date-that-is for more solutions about how to handle DTS changes.

Alex
  • 4,885
  • 3
  • 19
  • 39
  • I am still trying to find any reference/link from MSDN on why GetDate() and GetUTCDate returns different datetime under single select. However, this answer solved my issue for converting date to datetimeoffset. Thanks. – Bharathi Oct 16 '17 at 05:12
  • 1
    @Bharathi - I could not find any Microsoft references but there are a few existing question on SO dealing with the same issue: https://stackoverflow.com/questions/6036223/will-getutcdate-return-the-same-value-if-used-twice-in-the-same-statement/6036783#6036783 and https://stackoverflow.com/questions/3620105/sql-server-intrigued-by-getdate/3620119#3620119 – Alex Oct 16 '17 at 05:52