473,699 Members | 2,570 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DataSet DateTime Format

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?
Jan 11 '06 #1
3 9028
Has anyone else run across this?

Jan 11 '06 #2
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.
Jan 11 '06 #3
I also published the solution here:

http://ineedadip.blogspot.com/2006/0...e-problem.html
Jan 28 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
4023
by: andrewcw | last post by:
I read in a earlier post that I can get the column of a grid to sort by datetime if the column type was set as Date I deserialize my XML and one attribute of the XSD has type as dateTime but upon inspection and behavior in the gri the data type is string. If I preview the dataset with the GUI in VISSTUDIO it shows my datatype to be dateTime .. If I try to rest the datatype I get this erro Additional information: Cannot change DataType...
0
3445
by: vooose | last post by:
Suppose you have a DataTable which is part of a DataSet that has a column of type DateTime. When you call ds.GetXml() the date comes out looking something like this: 2005-01-17T23:01:34.9526250+11:00
6
11362
by: Helen | last post by:
I have an array being passed as a parameter to Crystal 9 (using vb .net in VS 2002). The listbox containing the dataset output at runtime shows DateTime format, but I want to only show mm/dd/yyyy format to the user. How is this accomplished? I changed the dataset type for the field to "Date" but it made no difference Tks.
26
2674
by: Reny J Joseph Thuthikattu | last post by:
Hi, I have a variabe in the format of 'DD-MON-YYYY HH:MI AM' .I want to add a miniute to it.How can i do that? by manipulation i want to make '01-JUNE-2004 11:59 PM' to '02-JUNE-2004 12:00 AM' How do i do that? Reny ---
1
1773
by: Scott M. Lyon | last post by:
Part of an application I'm working on needs to manipulate some XML data (in an XML file), by reading the data into a DataSet using the MyDataSet.ReadXML method, and then using MyDataSet.WriteXML to update the XML file itself. However, now I want to take advantage of the Primary Key functionality of the DataSet object, so I can easier find rows (in a DataTable in the DataSet), and also easier remove rows, based on a unique key.
3
1745
by: darrel | last post by:
I'm grabbing an XML string from a database and trying to pass it into a dataset. I've read this article: http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=213 and have tried to implement it as such: Dim strRdr As StringReader strRdr = New StringReader(DS.Tables(0).Rows(0)("xml").ToString)
0
1266
by: samtilden | last post by:
We have upgraded our web project to Visual Studio 2005 (from VS 2003) and are still interfacing with some legacy ASP code that we do not want to change. The problem is that the VS 2003 compiler used to serialize a DateTime as: 2006-06-07T15:00:00.0000000-04:00 Now, VS 2005 compiler is serializing a DateTime as:
4
1685
by: LW | last post by:
Hi! I have a DataSet and a class in my Web Service. My output for example is: <OrderInfo> <CustomerID>VINET</CustomerID> <OrderID>10248</OrderID> <OrderDate>07/07/1996</OrderDate> <ShippedDate>08/08/1996</ShippedDate>
1
1440
by: | last post by:
I'm querying Index Server to return search results, both regular properties and some custom properties I've created. Index Server has this preference for thinking about information as strings rather than datatypes.If you really work at it, you can configure Index Server to treat the data as, say, sortable DateTime datatypes, or integer datatypes. But I'm finding this a huge pain in the butt, and it bothers me that I'm attaching a bunch of...
0
8689
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8618
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9035
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7752
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6534
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4376
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4631
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3058
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2010
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.