Posts Tagged ‘sqldatetime’

Convert dotnet DateTime to Sqlserver SqlDateTime

Dec 9th, 2011

The SqlDateTime has a narrower span than the Dotnet ditto; dotnet starts from year 0 but sqlserver from 1753.  This means that uninitialised DateTime fields in dotnet will give runtime execution errors when trying to persist them in a sqlserver database.

I write together a small and simple method for making sure the DateTime interval is inside the valid SqlDateTime interval.

public static class DateTimeExtensions
    {
        /// <summary>This method returns the datetime fitting into min/max of Sqlserver
        /// as per http://codebetter.com/petervanooijen/2008/08/21/valid-date-time-values-in-sql-server-sqldatetime-vs-datetime/
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static DateTime ToValidSqlserverDatetime(this DateTime dt)
        {
            //  Must cast System.Data.SqlTypes.SqlDateTime.MinValue: http://codebetter.com/petervanooijen/2008/08/21/valid-date-time-values-in-sql-server-sqldatetime-vs-datetime/
            if (dt < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue)
            {
                return (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else if (dt > (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue)
            {
                return (DateTime)System.Data.SqlTypes.SqlDateTime.MaxValue;
            }
            else
            {
                return dt;
            }
        }
    }

The code is easier to read at pastebin.

Honour those who should.