Here we go, some important stuff about dates…..(it’s not only BizTalk related)
I had a very simple scenario.
- Webservice Receives a request (with several datetime fields in it)
- Send the received message to SQL via the WCF adapter
For some obscure reason some datetime values originating from the same WebRequest got modified once in the database and some did not.
After some tracing we found that the dates were serialized in a different way once inside the webservice.
some of the formats we saw :
All these got to the database unchanged, but we also saw datetime like below
- for all supported see : http://www.w3schools.com/schema/schema_dtypes_date.asp
And these datetime fields got calculated back to their UTC time and were then stored in the database.
After some experimenting we found the following interesting stuff…..
Datetime.Kind (but for sure, everybody already knew that)…. So what is this KIND ?
|Kind||Gets a value that indicates whether the time represented by this instance is based on local time, Coordinated Universal Time (UTC), or neither.|
So what happens is the following,
- Create a DateTime in code (like DateTime current = DateTime.Now) and Kind is Local
- Create a DateTime in code and Assign a database field (like DateTime fromdb = datetimefield from database) and Kind = Unspecified
And what’s the difference ?
Well the difference is that when the WCF Adapter stores the date to the database field.
- when != to unspecified it calculates it back to UTC
- when == unspecified it is stored as is….
Since I really wanted unified DateTimes in the complete solution I created a pipeline that did the following trick:
static string CreateUnspecifiedDate(string someXsDateTimeString)
DateTimeOffset dto = DateTimeOffset.Parse(someXsDateTimeString);
DateTime res = dto.LocalDateTime;
And the problem was solved. But I did learn something about datetimes