Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

reading date/time from xml Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 12:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 10:33 AM
Points: 66, Visits: 205
I have an xml with a date/time with offset:

<start>2014-07-01T13:00:00-06:00</start>

I'm reading this through openxml and my server is in CST:

declare @DT datetime2(0)

select @DT = start
from openxml ( @idoc, '/root', 1 )
with ( start datetime2 'start' )

When I query for @DT I get: 2014-07-01 19:00:00, which is the date/time in UTC, because of the 6-hour offset.

My understanding is that the XML is in UTC and the offset is provided to identify the time zone where the data was originated. I need to read the data such that is returns 2014-07-01 13:00:00.

Can someone please explain what is that I'm doing wrong?

Thanks
Post #1590460
Posted Friday, July 11, 2014 6:05 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
In

select @DT = start
from openxml ( @idoc, '/root', 1 )
with ( start datetime2 'start' )

Try changing the DATETIME2 portion to DATETIME2(0) to see if keeping the data type consistent helps.

OR In this case since you have the zone where the time is maybe try substituting datetime2 with DATETIMEOFFSET in the code , then in your select cast this to SMALLDATETIME. See if this helps you.
Post #1591830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse