Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Valid date-time values in sql server. SqlDateTime vs DateTime

You cannot store every date in sql server. The valid range of dates is from 1/1/1753 (1-1-1753) to 12/31/9999 (31-12-9999). The range of the .NET DateTime type is far larger. So before storing a datetime in a sql server database you have to perform a check. This should be (and is) not to difficult in .NET. But as the documentation of SqlDateTime and other Google results are confusing hereby a quick summary.

The .NET framework has two types, DateTime and SqlDateTime The SqlDateTime type has implicit operators which convert a sql datetime into a regular DateTime. Thanks to this implicit type conversion you can mix both date types in an expression. At first sight the follwing code looks like a good check.

DateTime bla = DateTime.MinValue;
if ((bla >= SqlDateTime.MinValue) && (bla <= SqlDateTime.MaxValue))
{
    // bla is a valid sql datetime
}

To my initial surprise it throws a sql exception: “System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.” What happens is that before comparing the date the variable bla will be (due to the implicit operator) cast to SqlDateTime. Doing that it will hit the the sqlexception. The rule is that in an expression with two different types they are converted to the narrowest of the two. So what will work is explicitly cast the SqlDateTime to a DateTime. Like this

DateTime bla = DateTime.MinValue;
if ((bla >= (DateTime)SqlDateTime.MinValue) && (bla <= (DateTime) SqlDateTime.MaxValue))
{
    // bla is a valid sql datetime
}

This behavior will not show up until the test meets an invalid sql date at runtime.The good thing is that this same kind of implicit conversion can prevent a compile.


 

The message is enigmatic until you start realizing that the implicit conversion of date leads to a different type for the result of the expression. A SqlBool instead of a .NET bool.

This function builds and runs well.

static bool isValidSqlDate(DateTime date)
{
    return ((date >= (DateTime)SqlDateTime.MinValue) && (date <= (DateTime)SqlDateTime.MaxValue));
}

Far easier than the many parsing and testing frenzy I found Googling on this.

This entry was posted in Coding, Data. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Jo Carm

    I just ate some great chili.

  • Xinteractx

    Improved it… to handle invalid dates too.     

      static bool isValidSqlDate(DateTime date)
            {

                //check for valid timestamp..
                try
                {
                    Convert.ToDateTime(date);
                    return ((date >= (DateTime)SqlDateTime.MinValue) && (date <= (DateTime)SqlDateTime.MaxValue));
                }
                catch
                {
                    return false;
                }

            }

  • Demo

    ‘ SELECT name FROM sys.tables order by modify_date

  • Koen R.

    This was exactly what I was looking for, thanks :)

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    OK, that sounds cool and OK regarding hibernate.

    But to keep on nagging, what about reports.. ..

  • Stefan Steinegger

    @pvanooijen: With NHibernate, you can map it as “Ticks”, then it stores the ticks as numbers, which avoids hacks and also is sortable and filterable. But – it is not readable if looking into the database.

    We have currently a solution with a custom DateTime type which rounds the value to 10ms precision (because of SQLServer precision mentioned by John Chapman) in order to only represent values that can be stored in SqlServer – and also checks the range. Then we map it with NHibernate using type=”Timestamp”. We avoid using MaxValue and stuff like this and use nulls instead. This also allows working not-null constraints.

  • http://jaychapman.blogspot.com John Chapman

    While not directly related to your post, also of interest is the fact that DateTime values in .NET are far more precise than DateTime values in Sql server. .NET stores times in 100-nanosecond increments, whereas SQL Server stores them in 3-millisecond increments.

    Note that Sql Server 2008 added datetime2 which addresses both of these issues.

  • Hans Kesting

    Instead of casting SqlDateTime.MinValue (which returns a SqlDateTime) you could also use SqlDateTime.MinValue.Value, which converts the SqlDateTime to a regular DateTime.

  • http://codebetter.com/blogs/peter.van.ooijen/ Peter van Ooijen

    Paw,
    I’m not very happy for such a solution as it requires some external code to understand what’s in your database.
    FI, the moment you start approaching your db with nHibernate (rather sooner than later) theres is going to be some hacking to do..

  • Paw

    I stoped using SQL DateTime a long time ago, unless I really need it for a SP. I save them as varchar and write my own converters to handle the conversion to eg. C#.
    The format could be yyyyMMdd-hhmmss or yyyyMMddhhmmss. That way you can still do an ORDER BY on them.

  • http://primedigit.com/ Will Shaver

    I’ve found that storing DateTime.MaxValue can cause trouble on comparison as well. Here’s the extension method that I use for this:

    public static class DateTimeExtensions
    {
    public static DateTime ToSQLDateTime(this DateTime dt)
    {
    return new DateTime(dt.Ticks – (dt.Ticks % 10000000));
    }
    }