How do you handle dates with OpenXML

Question

How do you handle dates with OpenXML

anglospice

SSC Enthusiast

Points: 109
More actions
November 3, 2005 at 3:49 pm

#166875

My data is being updated correctly for all fields except a date field. The program is crashing when a date is encountered with OpenXML. Is there a way to convert the date within SQL to handle this...I have tried the CONVERT statement below, but it does not work
UPDATE EducationSheetSchool
SET
EducationSheetSchool.Address= XMLEducationSheetSchool.Address,
EducationSheetSchool.[Name]= XMLEducationSheetSchool.[Name],
EducationSheetSchool.LEA= XMLEducationSheetSchool.LEA,
EducationSheetSchool.Tel= XMLEducationSheetSchool.Tel,
EducationSheetSchool.Email= XMLEducationSheetSchool.Email,
EducationSheetSchool.Start=CONVERT(varchar(30),XMLEducationSheetSchool.Start, 126),

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

What does one of the actual values look like?

I have had this problem when the date was formatted funny, like ddmmyyyy you may have to format the date client side prior to passing xml string to the db proc.

anglospice SSC Enthusiast Points: 109 More actions · Answer 2

Thanks for the reply - it is actually in the format 2003-09-21T00:00:00.0000000+01:00

Below is the dataset being passed as xml.

Everything else works fine - it just bombs out if I include the date. Alll data is coming from a vb.net datagrid.

"<NewDataSet>

<Name>St Johns</Name>

<Address>11 Whitehall</Address>

<LEA>LEA testxx</LEA>

<Email>sc@yahoo.com</Email>

<ReasonForLeaving>found better school</ReasonForLeaving>

</NewTable>

</NewDataSet>"

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Your going to have to perform manipulation to the date string application side prior to sending the xml to sql server.

The value you have does not work.

select convert(datetime,'2003-09-21T00:00:00.0000000+01:00')

This does works

select convert(datetime,'2003-09-21T00:00:00.000')

So strip off the last 11 characters.