Serializing System.DateTime for SQL Server

If you need to pass an entire object to a SQL Server stored procedure, one of the ways is to serialize it to XML and parse it in the SP then. I’ve used this approach several times for saving hierarchical data objects (let’s say an Order with a list of OrderLine objects) into the database. I’m not going to talk about pros and cons of this method, but rather share a solution to passing System.DateTime values.

So, what’s the problem with DateTimes, you might ask? Let’s suppose we have a class that looks like that:

public class MyClass
{
	public DateTime LocalDateTime
	{
		get;
		set;
	}
}

This class only exposes a single property that holds the local date and time. If we set it to DateTime.Now and serialize the whole object, the LocalDateTime value would look something like this:

2010-01-17T12:29:23.5561314+02:00

Now if you wanted to cast that string to DATETIME type in SQL Server (that’s what you do when you parse the serialized object’s XML inside a stored procedure), you would get an error message:

Conversion failed when converting datetime from character string.

After some playing, I found out that the string you try to cast must not have timezone information and only supports milliseconds. If you try to execute this statement:

select cast('2010-01-17T12:29:23.556' as DateTime)

... it will cast just fine. So, we need to get rid of the timezone information and cut the numbers to only milliseconds. The first one is easy. In you DateTime setter, use this code:

localDateTime = DateTime.SpecifyKind(value, DateTimeKind.Utc);

This just makes the DateTime value a UTC time and that means that timezone is +00:00 and after serialization, the value will now turn to:

2010-01-17T12:29:23.5561314Z

This gives you the local time without timezone information. Don’t worry about the ‘Z’ at the end. It is perfectly fine for SQL Server. If you want to convert the time to UTC first, you’d use:

utcDateTime = DateTime.SpecifyKind(value.ToUniversalTime(), DateTimeKind.Utc);

DateTime.ToUniversalTime() converts the DateTime value to universal time, the rest is the same.

We’re now left with the milliseconds issue. The numbers that appear after milliseconds are ticks. One tick is 10000 milliseconds, it’s the smallest unit of time. So we need to somehow cut the ticks information.

Since a millisecond is 10000 ticks, to drop the ticks portion of DateTime (to make it equal to 0), we can divide the ticks by 10000 and then multiply the result by 10000. Also, the System.DateTime struct gives us a handy constructor that takes the number of ticks and DateTimeKind value to construct the new DateTime. So we can use it like this:

localDateTime = new DateTime(value.Ticks / 10000 * 10000, DateTimeKind.Utc);

... which, when serialized would make our value look like this:

2010-01-17T12:29:23.556Z

which is now perfect to use in SQL Server.

I’m not sure if it’s the fastest and best way to deal with the ticks part of the serialized output. However, if you need only date, you can use DateTime.Date conbined with DateTimeKind.Utc and the output would be exactly what you need for SQL Server, there would be no need to deal with the ticks.

Here’s the complete source code for test console application:

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Xml.Serialization;
 
namespace DateTimeSerialization
{
	public class MyClass
	{
		private DateTime localDateTime;
		private DateTime utcDateTime;
 
		public DateTime LocalDateTime
		{
			get { return localDateTime; }
			set
			{
				localDateTime = new DateTime
				(value.Ticks / 10000 * 10000, DateTimeKind.Utc);
			}
		}
 
		public DateTime UtcDateTime
		{
			get { return utcDateTime; }
			set
			{
				long ticks = value.ToUniversalTime().Ticks / 
						10000 * 10000;
				utcDateTime = new DateTime(ticks, DateTimeKind.Utc);
			}
		}
	}
 
	class Program
	{
		static void Main(string[] args)
		{
			MyClass myClass = new MyClass();
 
			DateTime dateTime = DateTime.Now;
 
			myClass.LocalDateTime = dateTime;
			myClass.UtcDateTime = dateTime;
 
			StringBuilder builder = new StringBuilder();
			StringWriter writer = new StringWriter(builder);
 
			XmlSerializer serializer = new XmlSerializer(typeof(MyClass));
			serializer.Serialize(writer, myClass);
 
			Console.WriteLine(builder.ToString());
			Console.Read();
		}
	}
}

CodeProject