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!

NET DateTime, SQL dateTime, SQL date, a small Cheatsheet

Recently I blogged a little on my wrestlings with dates in SQL server. Some commenters suggested using a custom type instead of the sql datetime . Stefan pointed to the nHibernate Ticks type as a good way to handle the persistence of dates. Personally I keep using the sql date types, a main reason is compatibility with other date consuming software such as sql reporting services. Thank goodness my customers can handle the majority of their reporting themselves. They do understand a sql date(-time) type, they don’t understand how to convert a number of ticks to a order data. But I admit date(-times) are hard to work with. Hereby a public note on some of the other things I regularly run into.

The .Net framework has only one type, DateTime, SQL server has three.

DateTime. The range of valid values is smaller and the accuracy is lesser than the .NETDateTime. See my previous post and comments.

DateTime2.  Available since SQL 2008 has the range and accuracy of a .NET DateTime

Date. For values with just a date part. An order usually has an order date, but not an order time

Using a date literal in a sql statement.

Something you need for querying a database by hand looking for some special event. The syntax is somewhat hard to remember. There is a full overview accompanying the date type descriptions. The quick and dirty ‘odbc’-style usually works quite well.

SELECT Name WHERE Birthday = {d ‘2000-09-02′}

SELECT Description WHERE EventTime > {ts ‘2008-09-26 20:00:00′} AND EventTime < {ts ‘2008-09-26 21:00:00′}

Querying for a specific date.

Say you want to query orders on a specific date. The value to query for is coming in as a .NET datetime. When you would pass that directly to the sql query that would try to do something with the time part as well and miss some (or all) data. You have to take the Date part to get the intended data.

var cmd = new SqlCommand("SELECT Customer FRPM Orders WHERE OrderDate=@OrderDate");
cmd.Parameters.AddWithValue("@OrderDate", MyDateTimeParameter.Date);

Querying for a range of dates.

Say you want to select all events in a specific range of dates. Every event has a sqldatetime timestamp.

var cmd = new SqlCommand("SELECT Description WHERE TheDate >= @From AND TheDate < To");
cmd.Parameters.AddWithValue("@From", DateTimeFrom.Date.Date);
cmd.Parameters.AddWithValue("@From", DateTimeTo.AddDays(1).Date);

In case you don’t add one day to the end day you will miss some or all of the events on the last day due to the time part in the .net datetime parameter. The date part of a .net datetype matches 00:00 hours in the sql datetime.

As usual, all suggestions, corrections and additions to this cheatsheet are welcome.

This entry was posted in Coding, Data. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://www.binitex.com/TypeMaster/ Sergey

    What about this one?

    1. var cmd = new SqlCommand(“SELECT Description WHERE TheDate BETWEEN @From AND @To”);
    2. cmd.Parameters.AddWithValue(“@From”, DateTimeFrom.Date.Date);
    3. cmd.Parameters.AddWithValue(“@To”, DateTimeTo.AddDays(1).Date.AddMilliseconds(-1));

  • http://primedigit.com/ Will Shaver

    Here’s a little extension method I use to convert .NET DateTime objects into a format that can be saved to SqlServer 2005. Most dates don’t need it, but it is useful for the following:
    product.ExpiresDate= DateTime.MaxValue.ToSqlDateTime();
    or
    DateTime.MinValue.ToSqlDateTime();
    public static DateTime ToSqlDateTime(this DateTime dt)
    {
    if (dt < DateTime.Parse(“1/1/1753 12:00:00 AM”))
    dt = DateTime.Parse(“1/1/1753 12:00:00 AM”);
    return new DateTime(dt.Ticks – (dt.Ticks % 10000000));
    }