XML Query Help

Question

XML Query Help

SQL Server

Old Hand

Points: 377
More actions
May 31, 2019 at 8:18 pm

#3649158
Hi:
I have xml column with date in this format
```
<ns8:date>2018-07-31T00:00:00+02:00</ns8:date>
```
, when I parse the date with this statement
```
xmldata.value('(ns8:date)[1]', 'DATE') RDate
```
I get the RDate as 2018-07-30 instead of 2018-07-31.
When I use
```
xmldata.value('(ns8:date)[1]', 'DATETIMEOFFSET') RDate
```
I get the RDate as 2018-07-31 00:00:00.0000000 +02:00
I want the date to be 2018-07-31. How would my parse xml statement be?

Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

It requires two steps.

CAST(xmldata.value('(ns8:date)[1]', 'DATETIMEOFFSET') AS DATETIME) RDate

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

rVadim Hall of Fame Points: 3969 More actions · Answer 2

CAST and CONVERT ignore time zone, so just cast:

DECLARE @RDate DATETIMEOFFSET = '2018-07-31 00:00:00.0000000 +02:00';
SELECT CAST(@RDate AS DATE);

--Vadim R.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

how about this:

CAST(xmldata.value('(ns8:date)[1]','DATETIMEOFFSET') AS DATE) AS RDATE

Basically get the value in it's original format (DATETIMEOFFSET) and then cast that to a DATE.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.