Friday, August 3, 2012

Datetime in SQL/Biztalk and C#

I had a proc select a datetime field from a SQL database table and push it to a schema in biz. from there I grabbed it using xpath out of the schema as such:

tempstr = "string(/*[local-name()='Root']/*[local-name()='Record' ]/*[local-name()='TIME_STAMP']["+System.Convert.ToString(counter)+"]/text())";
time_stamp = xpath(Param_In,tempstr);

The above code is in an expression shape in an orchestration. Anyway, time_stamp , if you declare it as a C# DateTime gives you a time thats wrong. Actually its not far off, a few hours is all. I suspect 
time_stamp = xpath(Param_In,tempstr);
converts from UTC since the sql date time, on the right side of the equals, is of the format 2012-08-02T15:45:42.62Z. 
So I kept the time_stamp as a System.String and converted it using DateTime.TryParse. The 'T' indicates a placeholder that will not change, and the Z indicates UTC time. So the DateTimeStyle is RoundtripKind according to the surprisingly helpful page at msdn;

The following code converts from the sql string to the C# System.DateTime:

string sometime = "2012-08-02T15:45:42.62Z";
DateTime newDate;
bool success = DateTime.TryParse(sometime, null, System.Globalization.DateTimeStyles.RoundtripKind, out newDate);

No comments:

Post a Comment