Interesting observation with DATETIMEOFFSET data type in SQL

Calculating daily max values of a table will be different if you are using/storing the data in DATETIMEOFFSET format.

Basic difference is, while comparing the data between DATETIMEOFFSET and DATE (or DATETIME), DATETIMEOFFSET value will be converted to UTC and compared. Example: is greater than

We can use SWITCHOFFSET and convert DATETIMEOFFSET value to UTC and compare to address the problem.

Below is quick example where I had a challenge and the fix.


--

DECLARE @mydatetimeoffset DATETIMEOFFSET = '2017-11-05 21:30:00.00 -04:00'
DECLARE @mydatetimeLocal DATETIME = '2017-11-06 00:30:00.00'

SELECT @mydatetimeoffset as mydatetimeoffset, convert(date,@mydatetimeoffset) as mydatetimeoffset_date
SELECT @mydatetimelocal as mydatetimelocal, convert(date,@mydatetimelocal) as mydatetimelocal_date

if(@mydatetimeoffset > convert(date,@mydatetimelocal))
	SELECT '<' + convert(varchar(50),@mydatetimeoffset) + '> is greater than <' + convert(varchar(50),convert(date,@mydatetimelocal)) + '>'

	
	DECLARE @Days_DataCapture int = 7

-- problematic query
	SELECT EPlName , convert(date,EndDateTime) as DataDate ,max(EPValue) AS EPValue 
	from
	 Table ep WITH (NOLOCK)
	WHERE EndDateTime > convert(date,dateadd(dd,@Days_DataCapture*-1,getdate()))
	AND EndDateTime < convert(date,getdate())
	GROUP BY EPlName , convert(date,EndDateTime)
	ORDER BY  2 

-- Fix 
	DECLARE @Days_DataCapture int = 7
	SELECT EPlName , convert(date,SWITCHOFFSET (EndDateTime, '+00:00') ) as DataDate ,max(EPValue) AS EPValue 
	from
	 Table ep WITH (NOLOCK)
	WHERE EndDateTime > convert(date,dateadd(dd,@Days_DataCapture*-1,getdate()))
	AND EndDateTime < convert(date,getdate())
	GROUP BY EPName , convert(date,SWITCHOFFSET (EndDateTime, '+00:00') )
	ORDER BY  2 

--

FYI:
DatetimeOffset_1

Advertisements
This entry was posted in Script, SQL, SQL Query, Uncategorized and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s