Today whilst trying to validate an XML document in Sql Server 2005, which had been passed in as a paramater from .Net, I came accross an issue with DateTime serialization.

XML Date Time Sepcification

The W3C XML Schema Scpeification specifies that a valid datetime is the following

2008-04-28T22:45:32+01:00

The first portion before the ‘T’ is obviously the date. The second portion consists of the UTC time, plus an offset value. This allows times to be compared around in a standard manner, with the ability to obtain the local time. +01:00 means UTC+1, or British Summer Time for us in the UK.

Instead of having an offset of +00:00 for a UTC time with no offset, the specification allow for this to be replaced with a Z, so that

2008-04-28T22:45:32+00:00

becomes

2008-04-28T22:45:32Z

In addition, the scpeification allows for fractional seconds, so that a precise UTC+0 datetime can be represented as

2008-04-28T22:45:32.1845Z

Where does .Net & SQL fit into all of this?

Well, imagine like me you want to take advantage of the XML schema feature of Sql Server 2005. You can create a schema, register it within 2005 and then rather than pass in individual paramaters to a stored procedure, you can pass in an XML document.

Consider the implications this has when inserting data like a Shopping Bag object. Typically you would insert the Shopping Bag Order Header first to retrive some form of reference id from the database, and then iterate over each of the Order Lines, creating a command and inserting one at a time. For each order line you will be firing off TCP packets to the database, and each packet will contain some overhead. Having to wait for a reply from the database prior to inserting the next Order Line can also cause a delay.

So, rather than processing each line at a time, we could make our Shopping Bag object serializable, create an XML schema to validate this Shopping Bag, register the schema with the database and create a Sql Script to validate and parse the serialized XML and insert the data into the correct tables (using the OPENXML methods).

We don’t have to make multiple calls to the database, reducing the chance of failure and any application could call the Stored Procedure without having to know the specific order in which data needs to be inserted. With the typical method we need to call the stored procedure to insert the order Header first and then insert the Order Lines; with the XML method all of this is handled within the stored procedure.

More than likely your Shopping Bag object will have a DateTime type property. When .Net serializes this property it will be a standard XML Schema Specification valid DateTime, with a fractional component and a DateTime offset, like 2008-04-28T22:45:32.0128+01:00

T-SQL however cannot cast this format to a T-SQL DateTime, and although the XML you pass to Sql will validate against the XML Schema held in the database, you won’t be able to insert the value into a column. What T-Sql actually requires is the following 2008-04-28T22:45:32Z. No fractional component and no UTC offset. Once the date is in the database all concept of UTC is lost, T-SQL doesn;t not understand what UTC is.

Becuase you’re going to be using the XMLSerializer class to serialize the Shopping Bag object you can’t control how the DateTime property is serialized, there’s no ‘XmlDateTime’ attribute. Instead, we have to format the data in the get method of our property.

public DateTime _myDate;

public DateTime MyDate
{
get { return DateTime.SpecifyKind(DateTime.Parse(_myDate.ToString(“u”)), DateTimeKind.Utc); }
set { _myDate = value; }
}

_myDate.ToString(“u”) makes sure we lose any fractional component. The format string u is known as an invaritant DateTime format. This does not however remove the offset value. Todo this we need to tell .Net that this is a UTC timezone time (rather than British Summer Time, UTC+1), hence the use of DateTime.SpecifyKind().

Now when this is serialized .Net will serialize our DateTime as a T-SQL compabtible datetime., and we havn’t lost the exact datetime that was originally set, however we would need to create method to access this.