A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Wednesday, November 18, 2009

SqlDateTime to DateTime

My coworker was having fun working with SqlDateTime and DateTime
structures and he asked me just WTF they were doing in
http://msdn.microsoft.com/en-us/library/aa326475(VS.71).aspx He was
attempting to convert a SqlDateTime into a DateTime and the syntax
just was not clicking. Didn't for me either until I read up on what
the "explicit" meant.
http://msdn.microsoft.com/en-us/library/xhbhezf4(VS.71).aspx

What's all the hubbub? SqlDateTime allows for nulls, DateTime does
not. They're both value types but SqlDateTime implements INullable so
it gets away with it. The $64,000 question then became what happens
when you assign a "null" SqlDateTime to a DateTime and the answer is
it converts it to a sentinal value of 1900-01-01T00:00:00.000

public static void ConvertDates()
{
    // create a sql datetime object with today's date
    System.Data.SqlTypes.SqlDateTime sdt = new System.Data.SqlTypes.SqlDateTime(2009, 11, 18);

    // Re-assign the type as null
    sdt = System.Data.SqlTypes.SqlDateTime.Null;

    // Create a datetime variable and set value to max (9999-12-31T23:59:59)
    DateTime dt = DateTime.MaxValue;

    // A null SqlDateTime is converted to the following datetime {1/1/1900 12:00:00 AM}
    dt = (DateTime)sdt;
}

I wonder whether using your own sentinal value wouldn't be better. As
the above is the minimum date/time value in SQL Server for a datetime,
one could assume that a DateTime (.NET) value of less than the minimum
means NULL. And it's days like today that make me happy and slightly
regretful that I don't deal as deeply in the C# world here at work.

No comments: