SQL Server DateDiff between two dates returns inaccurate values

Question

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

Answer 1

@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.