CODEWITHSUNDEEP
sqlsql-server-2008t-sqlutcsql-server-openxml
user1327064
user1327064

Reputation: 4337

How to convert UTC to local time while reading XML using OpenXML?

Data is stored as XML (Sample):-

<root>
    <data>
        <checked>true</checked>
        <datetimestamp>2016-10-18T08:11:12-06:00</datetimestamp>
    </data>
    <data>
        <checked>true</checked>
        <datetimestamp>2016-10-18T08:13:20-06:00</datetimestamp>
    </data>
</root>

I am reading data using OpenXML:-

DECLARE @XData XML
DECLARE @doc INT

DECLARE @TempXML TABLE
(
    checked BIT,
    datetimestamp DATETIME,
)  

SELECT @XData = Data FROM Table1 WHERE ID = @ItemId

EXEC sp_xml_preparedocument @doc OUTPUT, @XData

INSERT INTO @TempXML(checked,datetimestamp)  
    SELECT * FROM OPENXML (@doc, '/root/data',1)
    WITH (
    checked BIT 'checked',
    DateTimeStamp DATETIME 'datetimestamp',
    )

DateTime is stored as UTC in XML. In above SP I am reading xml data using openXML and storing data in a temp table. I need to convert UTC datetime into local time while inserting. How can I do that. Following line of code convert UTC to local time zone perfectly but could not figure out how to use it with Openxml:-

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), MyTable.UtcColumn) 
       AS ColumnInLocalTime
FROM MyTable

Upvotes: 0

Views: 613

Answers (1)

iamdave
iamdave

Reputation: 12243

Can you not just specify your columns in your insert statement?

INSERT INTO @TempXML(checked,datetimestamp)
    SELECT checked
            ,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), datetimestamp) as datetimestamp
    FROM OPENXML (@doc, '/root/data',1)
    WITH (
    checked BIT 'checked',
    DateTimeStamp DATETIME 'datetimestamp'
    )

Upvotes: 0

Related Questions