Convert chr from xml file in storeproc to datetime

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 2000 Forums

SQL Server Development (2000)

Convert chr from xml file in storeproc to datetime

Author

Topic

clean
Starting Member

7 Posts

Posted - 2006-02-16 : 11:00:48

I have the following proc to take an xml string and insert into the database. The xml string has a datetime value and when i try to insert it give me a problem. Anybody know why this is happenig or what i am doing wrong.

Here is the proc:

DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT, @items

--This code inserts new data.

Insert Into absorbentorder
SELECT ponumber, dpc, absorbentcode, quantity, convert(varchar(12),orderdate,103)
FROM OPENXML (@hDoc, '/absorbentitems/item',2)
WITH (ponumber char(10), dpc char(15), absorbentcode char(15), quantity char(15), orderdate varchar(12))

EXEC sp_xml_removedocument @hDoc

Here is the xml string parameter beging passed:

<absorbentitems><item><ponumber></ponumber><dpc>501</dpc><absorbentcode>CL1025</absorbentcode><quantity>6</quantity><orderdate>15/02/2006</orderdate></item><item>

I am passing the short date as a test but from my program it is passing date and time.

Any suggestions would be great.

Thanks!

Alex

clean
Starting Member

7 Posts

Posted - 2006-02-16 : 11:11:03

Never mind figured it out, it was the format of which the date was going in, it was looking for mm/dd/yyyy not dd/mm/yyy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-17 : 02:30:33

You will be on safer side if you use universal format yyyy-mm-dd or yyyymmdd

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-06-02 : 13:41:14

"it was looking for mm/dd/yyyy not dd/mm/yyy"

To the best of my knowledge neither of those are valid for XML, so you will be relying on the native Locale settings for SQL Server as to how that gets processed. Better to use standards compliant date formats for XML - which I think is in the style of:

<tag>2004-07-26T18:42:10Z</tag>

Using a properly formed Schema would provide alerts to data which is non-conforming, and help catch any duff-data

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources