openxml date conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

openxml date conversion

Author

Topic

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 10:33:25

I'm in the process of doing a database insert from xml to several tables. All has gone well until I got to trying to insert a date. Because of the requirements of my customer, i have the date as a string like

<E05>
<E05_02>01312009212529</E05_02>
<E05_04>01312009212542</E05_04>
<E05_05 />
<E05_06 />
<E05_07 />
<E05_09 />
<E05_10 />
<E05_11>01312009213000</E05_11>
<E05_13>01312009213000</E05_13>
</E05>

my sp looks like

INSERT INTO E05 (pk_E01, E05_02, E05_04, E05_05, E05_06, E05_07, E05_09, E05_10, E05_11, E05_13)

SELECT @ParentID, E05_02, E05_04, E05_05, E05_06, E05_07, E05_09, E05_10, E05_11, E05_13)
FROM OPENXML(@index, 'DATA_RECORD/E05', 2)
WITH (pk_E01 INT, E05_02 datetime, E05_04 datetime, E05_05 datetime, E05_06 datetime, E05_07 datetime, E05_09 datetime, E05_10 datetime, E05_11 datetime, E05_13 datetime)

I've tried something like

INSERT INTO E05 (pk_E01, E05_02)
select @ParentID,convert(datetime, replace([E05_02], 'T', ' ')) [E05_02] with no luck. I'm not sure what the format should look like.

Thanks.

Frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:35:44

what does value like 01312009212542 represent? is it 31/01/2009 21:25:42?

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 10:54:30

Yes, well actually 1/31/2009 09:25:29 PM. I get that into the xml in some sp code like

dbo.format_date(E05.E05_02)

I'm reversing the process here to move data around between db's.

Frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 11:24:04

then make use of convert to convert it to valid datetime value

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 12:21:25

Yes, but I wasn't sure of what the syntax is.

select @ParentID, convert(datetime, E05_02, 109) Doesn't work

Frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 12:37:39

[code]select @ParentID, convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,'/',0),6,'/',0),11,' ',0),14,':',0),17,':',0), 101)... [/code]will work i guess

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 13:27:53

I was working on this solution and borrowed you syntax. It didn't work and I thought that

select @ParentID, convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':'), 110)

should be closer, but it didn't work either. I know this is the right track because

SELECT @ParentID,convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

works, but it doesn't have time.

Frank

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 13:32:56

There doesn't seem to be a format of mm-dd-yyyy hh:mm:ss ??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 13:39:59

then try this too:-


select @ParentID, DATEADD(ss,SUBSTRING(E05_02,13,2),DATEADD(mi,SUBSTRING(E05_02,11,2),DATEADD(hh,SUBSTRING(E05_02,9,2),DATEADD(dd,SUBSTRING(E05_02,3,2)-1,DATEADD(mm,LEFT(E05_02,2)-1,DATEADD(yy,SUBSTRING(E05_02,5,4)-1900,0)))))) FROM ....

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 13:40:59

Make use of two CONVERT options then..and concatenate them??

declare @d varchar(50)
select @d = '01312009212529'
select convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(@d,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),110) + ' ' + convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(@d,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),108)

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 14:24:29

It's been a year or two since I did this web site and I had forgotten that I had to build a db function to put the date into this form, so of course there isn't a standard conversion.

The second approach worked, but when I plugged in the field value it failed again. Not sure why yet. They are the same string. I really appreciate your help on this.

Frank

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 15:17:50

quote:
The second approach worked, but when I plugged in the field value it failed again

Whats the error you received?

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 15:33:54

I get the conversion error message "Conversion failed when converting datetime from character string." which is what I have been getting all along.

That's with this code

SELECT @ParentID,convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),110) + ' ' + convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),108)
FROM OPENXML(@index, 'DATA_RECORD/E05', 2)
with (E05_02 datetime)

What works is the code that I copied from the post

declare @d varchar(50)
select @d = '01312009212529'
SELECT @ParentID,convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(@d,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),110) + ' ' + convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(@d,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),108)
FROM OPENXML(@index, 'DATA_RECORD/E05', 2)
with (E05_02 datetime)

I've verified that the strings are identical by stuffing the datetime string into a varchar field.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 16:01:30

Try changing it to....

ELECT @ParentID,convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),110) + ' ' + convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),108)
FROM OPENXML(@index, 'DATA_RECORD/E05', 2) 
with (E05_02 varchar(20))

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 16:07:01

This works for me

create table #temp (date datetime)
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<E05>
<E05_02>01312009212529</E05_02> 
<E05_04>01312009212542</E05_04> 
<E05_05 /> 
<E05_06 /> 
<E05_07 /> 
<E05_09 /> 
<E05_10 /> 
<E05_11>01312009213000</E05_11> 
<E05_13>01312009213000</E05_13> 
</E05>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

insert into #temp (date)
SELECT convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),110) + ' ' + convert(varchar(10),convert(datetime, STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')),108)
FROM OPENXML(@idoc, 'E05', 2) 
with (E05_02 varchar(20))

select * from #temp

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-07 : 16:34:33

any possibility you can change your xml to bring in the date string the way you want it in the first place? I mean why is it that way in the first place? do you have control over the xml itself? what produces it

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 16:54:05

No, we realized early on that this was a demented use of datetime, but we have to satisfy a govt. program which reads our xml and build it's own database. I think the programmers just declared the format without really knowing much about SQl or XML. The were a lot of anomalies with their XSD.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-07 : 17:00:02

how about this, I would put that whole ugly concatenation into a function maybe

SELECT SUBSTRING(E05_02,1,2) + '-' + SUBSTRING(E05_02,3,2) + '-' + SUBSTRING(E05_02,5,4) + ' ' + SUBSTRING(E05_02,9,2) + ':' + SUBSTRING(E05_02,11,2) + ':' + SUBSTRING(E05_02,13,2)
FROM OPENXML(@idoc, 'E05', 2)
with (E05_02 varchar(20))

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

ftsoft
Starting Member

22 Posts

Posted - 2010-01-07 : 17:02:12

vijayisonly

Yes, that seems to work I must have misunderstood the purpose of the with statement

with (E05_02 VARCHAR(20)) works

whereas with (E05_02 datetime doesn't)

Frank

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 17:04:24

Yes, you are trying to read the value 01312009212529 as a datetime which was throwing you off...
Glad we cud resolve it.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-07 : 17:08:33

As a matter of fact, now you could just simplify the SELECT to..

insert into <urtable> (<datetimefield>)

SELECT STUFF(STUFF(STUFF(STUFF(STUFF(E05_02,3,0,'-'),6,0,'-'),11,0,' '),14,0,':'),17,0,':')
FROM OPENXML(@idoc, 'E05', 2) 
with (E05_02 varchar(20))

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources