Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

BizTalk SQL Time format

Dan Field

Short and sweet today – there are several blog and forum posts about how to process SQL DateTime fields using BizTalk.  It generally comes down to parsing the string value into a DateTime object and returning .ToString(“s”).  Today I was trying to insert into a TIME(0) column.  I know in ADO.NET you can pass a TimeSpan into this, but parsing the string into a TimeSpan and returning TimeSpan.ToString() didn’t work (nor did DateTime.ToString(“s”)).

One option is to simply change the generated schema so that it uses an xs:string and a VARCHAR of the appropriate length instead of the generated “duration” format. The string could then be CAST()ed to the correct format in TSQL; however, if we look at the pattern expression for the duration format we get this:

\-?P(\d*D)?(T(\d*H)?(\d*M)?(\d*(\.\d*)?S)?)?

Meaning the adapter/serializer expects

18:05:04

to be formatted as

PT18H05M04S

.  A simple .NET helper method called from a Scripting functoid does the trick:

public static string SqlTime(string d)
{
  DateTime dt;
  if (DateTime.TryParse(d, out dt))
  {
    return dt.ToString(@"\P\THH\Hmm\Mss\S");
  }
  else
  {
    return "";
  }
}

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\