Is SQL datetime2/datetimeoffset conversion deterministic? May 28th, 2013

Vinod Kumar

Recently in a customer code review I came across some interesting logic about Datetime2 and DatetimeOffset datatype. Here is the problem statement for your reference:

Customer was trying to create a view that contains a computed column that converts a datetime2(7) column to a datetimeoffset(7) column. The CAST function looks like:

CAST((CONVERT(varchar(255), DATEADD(hh, +8, RawLog.[Created]),126) + ‘+08:00’) AS datetimeoffset(7)) AS [Created_GMTPlus8]

This returned them an error when a index was created on this view:

Cannot create index on view "MyVideo..RawLogView". The view contains a convert that is imprecise or non-deterministic.

So the customer was asking, how to make this deterministic? Would love to learn if you have any other option.

Possible Solution

Here are couple of possible solutions to this problem. The root reason is that the cast string to datetimeoffset(7) is not deterministic,  we need use convert with style 126.  Let me show how we can rewrite this query to achieve the same. Note, in below example Option 2, we use SWITCHOFFSET built-in to convert a datetime UTC values to a datetimeoffset, and then SWITCH the timezone to +08:00. Let us see the code now:

USE tempdb

GO 

— If it exists Drop it.

DROP TABLE rawlog

GO 

CREATE TABLE rawlog (id INT IDENTITY(1,1) PRIMARY KEY,

            created DATETIME2(7))

GO

INSERT INTO rawlog VALUES(SYSDATETIME())

GO

We have created our dummy table for use with a value. Let us check if the string conversion returns the same result.

— Make sure SWITHOFFSET return the same value as we expected

SELECT created,CAST(CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’ as DATETIMEOFFSET(7)) CASTStringValue,

SWITCHOFFSET(CAST(created AS DATETIMEOFFSET),‘+08:00’)  AS result FROM dbo.rawlog

GO

Our output looks like:

image

Our values are exactly the same. Let us create the view next, and we will then check if it can be indexed as per our problem statement.

CREATE VIEW v1 WITH SCHEMABINDING AS

  SELECT CAST(CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’ as DATETIMEOFFSET(7)) AS result

  FROM dbo.rawlog

GO

–return 0 because of the case string to datetimeoffset is not deterministic

SELECT OBJECTPROPERTY(object_id(‘v1’),‘IsIndexable’)

GO

Option 1:

Next let us try to use the proper Conversion function and check. First add the offset with DATEADD and then convert it into DATETIMEOFFSET(7).

CREATE VIEW v2 WITH SCHEMABINDING AS

  SELECT CONVERT(DATETIMEOFFSET(7), CONVERT(VARCHAR(255), DATEADD(hh, +8, [Created]),126) + ‘+08:00’,126) AS result

  FROM dbo.rawlog

GO

–return 1 because convert with 126 is deterministic

SELECT ObjectProperty(object_id(‘v2’),‘IsIndexable’)

GO

Option 2:

Other than using convert with 126 style, we can also use SWITCHOFFSET function which is also deterministic

CREATE VIEW v3 WITH SCHEMABINDING AS

  SELECT SWITCHOFFSET(CAST(created as datetimeoffset),‘+08:00’)  AS result FROM dbo.rawlog

GO

— Return 1 because SWITHCHOFFSET is deterministic

SELECT OBJECTPROPERTY(OBJECT_id(‘v2’),‘IsIndexable’)

GO

These are just two of the methods to achieve the goal. Do let me know if you use Indexed views in your code today? What are the scenarios you use them? Have you encountered any of these deterministic restrictions in your coding anytime? Do let me know, would love to learn from you too.

Tags: , , , , , , , , ,

This entry was posted on Tuesday, May 28th, 2013 at 09:57 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply