DataSet DateTime Format

[INeedADip]

I have seen this problem posted all over, but have never ran across a
solution....
I am serializing my dataset and they look like this:

<NewDataSet>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="List_101">
<xs:complexType>
<xs:attribute name="InvoiceID" type="xs:int" />
<xs:attribute name="Email" type="xs:string" />
<xs:attribute name="DomainID" type="xs:int" />
<xs:attribute name="SourceID" type="xs:int" />
<xs:attribute name="TypeEnum" type="xs:int" />
<xs:attribute name="ImportID" type="xs:int" />
<xs:attribute name="DateStamp" type="xs:dateTime" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<List_101 EmailID="InvoiceID" [email protected] DomainID="2421"
SourceID="12" TypeEnum="2" ImportID="20"
DateStamp="2005-09-18T17:39:00-07:00" />
</NewDataSet>

This blows up in SQL when I call "sp_xml_preparedocument".
It has trouble converting the DateStamp (because of the format)...
If I put it in Query Analyzer and remove "-07:00" from the DateStamp it
works fine....

How do I get rid of that? I don't want to parse every file that I export,
because I'm doing a ton of them.
Is there something I can do to the DataSet??? I tried things such as:
ds.Locale = CultureInfo.InvariantCulture;
But that doesn't work, and I don't know why it would because I don't even
know what that means. I thought the "-07:00" offset might be a culture
thing....nope....

Does anyone have any ideas?

 

[INeedADip]

Has anyone else run across this?

 

[INeedADip]

Although I didn't "solve" the problem, I figured out how to work around it.
If you paste this in Query Analyzer it should work:
---------------------------------------
declare @x nvarchar(4000)
Select @x = N'<NewDataSet>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="List_101">
<xs:complexType>
<xs:attribute name="InvoiceID" type="xs:int" />
<xs:attribute name="Email" type="xs:string" />
<xs:attribute name="DomainID" type="xs:int" />
<xs:attribute name="SourceID" type="xs:int" />
<xs:attribute name="TypeEnum" type="xs:int" />
<xs:attribute name="ImportID" type="xs:int" />
<xs:attribute name="DateStamp" type="xs:dateTime" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<List_101 InvoiceID="19066" Email="(e-mail address removed)" DomainID="2421"
SourceID="12" TypeEnum="2" ImportID="20"
DateStamp="2065-09-18T17:39:00-07:00" />
</NewDataSet>'

Declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT, @x
-- Insert Into List_101
Select
InvoiceID, Email, DomainID, SourceID, TypeEnum, ImportID,
CAST(left(DateStamp,10) AS datetime)
From
OPENXML(@hDoc, '/NewDataSet/List_101')
WITH (
InvoiceID Integer,Email varchar(50),DomainID Integer,SourceID
Integer,TypeEnum Integer,ImportID Integer,DateStamp varchar(10))

Exec sp_xml_removedocument @hDoc
---------------------------------------

I was trying to insert the XML data into a table called List_101, and that
table has a 'DateStamp' column that is a datetime type.
I kept getting the "Syntax error converting datetime from character string."
because of the DateTime format from DataSet.WriteXML();

So I ended up using the CAST(left(DateStamp,10) AS datetime) so it was in
the correct format and changed the WITH to DateStamp varchar(10).
The sp_xml_preparedocument doesn't blow up anymore and the data gets
successfuly inserted into List_101 with the proper DateStamp (which is a
datetime in the table) because it converts the varchar(10) on its own.

Hopefully this helps the next person.

 

[INeedADip]

I also published the solution here:

http://ineedadip.blogspot.com/2006/01/spxmlpreparedocument-date-problem.html