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="u rn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSe t" msdata:IsDataSe t="true"
msdata:UseCurre ntLocale="true" >
<xs:complexType >
<xs:choice minOccurs="0" maxOccurs="unbo unded">
<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:dateTi me" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<List_101 EmailID="Invoic eID" Em************@ joe.com 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_prepare document".
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.Inv ariantCulture;
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="u rn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSe t" msdata:IsDataSe t="true"
msdata:UseCurre ntLocale="true" >
<xs:complexType >
<xs:choice minOccurs="0" maxOccurs="unbo unded">
<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:dateTi me" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<List_101 InvoiceID="1906 6" Email="jo*@joe. com" DomainID="2421"
SourceID="12" TypeEnum="2" ImportID="20"
DateStamp="2065-09-18T17:39:00-07:00" />
</NewDataSet>'

Declare @hDoc int
exec sp_xml_prepared ocument @hDoc OUTPUT, @x
-- Insert Into List_101
Select
InvoiceID, Email, DomainID, SourceID, TypeEnum, ImportID,
CAST(left(DateS tamp,10) AS datetime)
From
OPENXML(@hDoc, '/NewDataSet/List_101')
WITH (
InvoiceID Integer,Email varchar(50),Dom ainID Integer,SourceI D
Integer,TypeEnu m Integer,ImportI D Integer,DateSta mp varchar(10))

Exec sp_xml_removedo cument @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.WriteXM L();

So I ended up using the CAST(left(DateS tamp,10) AS datetime) so it was in
the correct format and changed the WITH to DateStamp varchar(10).
The sp_xml_prepared ocument 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/0...e-problem.html