Tallan Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

BizTalk SQL Time format

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:


Meaning the adapter/serializer expects


to be formatted as


.  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");
    return "";
Share this post:

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>