- This topic has 2 replies, 1 voice, and was last updated 9 years, 6 months ago by
.
-
Posts
-
-
Hi,
Hoping someone could help me with an issue I'm experiencing. I have a comma delimited text with multiple columns that i insert into a SQL 2000 table using Biztalk 2006. One of the columns has a date eg 22/12/2006. When Biztalk inserts this into the SQL table I get a 'Arithmetic overflow error converting expression to data type datetime'. Yet if I change the format to 12/22/2006. Biztalk is able to Insert the data.
Now this is were I'm stumped. I'm running Biztalk 2006 and SQL 2000 locally on my PC. My regional settings are set to Australia. Hence short date set to dd/mm/yyyy. If I look at my table in SQL and check data it is dd/mm/yyyy.
So to troubleshoot I created a datapump from SQL and I successfully imported the 22/12/2006 format into the table from the same text file. i then changed the text file to be 12/22/2006 and ran the data pump. This too was successful but changed the format to be 22/12/2006. I then tried it through Biztalk and it was inserted and the data appeared as 22/12/2006.
All the Biztalk schemas for the field are set to string and when viewing the message through the admin console the dates are in the correct format as to the text.
Hopefully someone can shed some light. At the moment I'm at a loss. Where is it getting converted? I cannot see how this is happening. This is probably an issue with my SQL settings.
Thanks and regards
Rob
-
Rob,
My guess it has something to do with this:
A Receive error message "Syntax error converting datetime from character string" occurs while sending updategrams to the SQL send adapter.
This problem can occur if The SQL table contains a datetime column and the updategram is trying to update that column with an incorrect value.
To resolve this issue, do not use the BizTalk Mapper functoids to create the datetime values to map to the updategram. The functoids create a datetime value in the format "1999-05-31T13:20:00.000-05:00". SQL datetime columns require datetime values to be of the format "1999-05-31T13:20:00.000". Instead of using the default date and time functoid, create the datetime value manually by calling the Parse or ParseExact method of the DateTime class.
You may have to play with the System.Xml.XmlConvert class to handle cases where the day and month are getting confused.
Doug
-
A "quick" suggestion is to create a custom DateTime functoid. I had a similar scenario when moving data into Oracle, which has "wonderful" requirements for date and time formats. The best part is that once Date/Time is in the xsd:dateTime format, it goes in without fail. Now the only trouble is stringing together all the functoids in a map to make it work, hence the first solution was to use a scripting functoid. Of course, since I need this repeatedly, a .NET component and conversion functoid were completed that day.
If you like the idea, the SDK gives enough examples to get started. The only real work is in creating the few lines it takes to convert the formats. My solution was to allow a string to accept any valid .NET dateTime format and use the built-in methods to produce the xsd format. It wasn't pretty and can use some cleanup, but here's the basics…
public string ConvertStringToXsdDateTime(string sDateTime)
{
DateTime dReturn = new DateTime();
dReturn = DateTime.Parse(sDateTime);
return dReturn.ToString("s")
+ TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now).ToString().TrimEnd('0').TrimEnd(':');
}It works for me in EST/EDT zone, but I haven't attempted real testing. Hopefully someone will attempt this and find a more "perfect" solution and post it here.
BizTron
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.