Convert dotnet DateTime to Sqlserver SqlDateTime
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.
Tags: datetime, dotnet, sqldatetime, sqlservcer