CODEWITHSUNDEEP
sqlsql-serverstored-procedures
joebohen
joebohen

Reputation: 143

Correct Handling of Dates in XML table

I have the following query which fails due to the conversation of the date value. How do I correctly handle the dates in an xml table?

 DECLARE @x xml   
SET @x='<?xml version="1.0" ?>
<Positions>
<Position>
<DateOfFix>27/07/2018 16:29:58</DateOfFix>
<DayDate>12/02/2019</DayDate>
<Latitude>53.6152267456055</Latitude>
<Longitude>-0.664610505104065</Longitude>
<VehicleId>789</VehicleId>
<Registration>XZ63HYF</Registration>
<Location>Park Farm Road, Scunthorpe, DN15 9, United Kingdom</Location>
<TrackedBy>VehicleTracker</TrackedBy>
<TrackerId>0</TrackerId>
</Position>
</Positions>

SELECT T.c.query('VehicleId').value('.', 'int') as VehicleId
    , T.c.query('Registration').value('.', 'nvarchar(10)') as Registration
    , T.c.query('Location').value('.', 'nvarchar(250)') as [Location]
    , T.c.query('DateOfFix').value('.', 'nvarchar(25)') as DateOfFix
    , Cast(T.c.query('DateOfFix').value('.','nvarchar(20)') as Datetime) as DateOfFix
    , T.c.query('TrackedBy').value('.', 'nvarchar(25)') as TrackedBy
    , T.c.query('trackerId').value('.', 'int') as TrackerId
    , T.c.query('Longitude').value('.', 'decimal(18,14)') as Longitude
    , T.c.query('Latitude').value('.', 'decimal(18,14)') as Latitude 
FROM @x.nodes('/Positions/Position') T(c)

Upvotes: 1

Views: 44

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

The problem is your dateformat in the xml. Since it is not ANSI compliant you will have to use convert and add the style. https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

This should work for you:

convert(datetime, T.c.query('DateOfFix').value('.','nvarchar(20)'), 103) as DateOfFix

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

Notice the Set DateFormat DMY

Set DateFormat DMY

SELECT T.c.query('VehicleId').value('.', 'int') as VehicleId
      ,T.c.query('Registration').value('.','nvarchar(10)') as Registration
      ,T.c.query('Location').value('.', 'nvarchar(250)') as [Location]
      ,T.c.query('DateOfFix').value('.', 'nvarchar(25)') as DateOfFix
      , Cast(T.c.query('DateOfFix').value('.','nvarchar(20)')   as Datetime) as DateOfFix
      ,T.c.query('TrackedBy').value('.', 'nvarchar(25)') as TrackedBy
      ,T.c.query('trackerId').value('.', 'int') as TrackerId
      ,T.c.query('Longitude').value('.', 'decimal(18,14)') as Longitude
      ,T.c.query('Latitude').value('.', 'decimal(18,14)') as Latitude 
 FROM   @x.nodes('/Positions/Position') T(c)

Returns

enter image description here

Upvotes: 1

Related Questions