Blog

Writing queries with date and time data types can be tricky! Join Kendra to learn tips and best practices for selecting the right data types, choosing the best performing functions to round (or truncate) values, and how to avoid common pitfalls with dates and times in SQL Server. If you have one year of experience writing T-SQL queries, this free 30 minute webcast is for you.

Notes and Links

Datetimeoffset

A quick note about datetimeoffset. This is one of the more confusing topics of the webcast, because figuring out how to make applications timezone aware is tricky. A rule of thumb: if you truly need to store the time zone “offset” of the value from where the data originated for legal or research purposes (“What time did the data appear to be from the perspective of the user?”), then this is the datatype for you.

For display purposes in most applications, it’s more efficient to normalize off the time zone preference of the individual user in a separate table, then store datetime in a single standardized timezone like UTC.  Essentially, since people travel around and governments change timezones, persisting the information of what timezone someone was in when they did something is usually not worth it, because it’s usually not valuable information.

A great detailed discussion (with cartoons!) of circumstances where you might choose datetimeoffset vs datetime/datetime2 is in this StackOverflow question.

Posters

Poster downloads are over this way.

For more videos like this:

↑ Back to top
  1. I always love your presentations Kendra! Nice!

  2. POSTER DOWNLOADS LINK 404′S.

  3. Kendra, I love your presentations too.
    You noted that CAST(0 as datetime), CAST(0 as smalldatetime) gives you January 1, 1900.
    Did you mention the following?
    Casting 0 as date, datetime2, datetimeoffset or time is not allowed.
    The range of dates for datetime is January 1, 1753, through December 31, 9999.
    The range of years for date, datetime2 and datetimeoffset is 1 to 9999, (January 1, 0001 to December 31, 9999).
    The range for smalldatetime is only January 1, 1900, through June 6, 2079.

    What happens in DATEADD() on datetime2 values around the 1752 discontinuity?

    • I see the ranges in the downloadable poster.
      :-)

    • I wish .Net 4.0 had a date data type comparable to that in SQL Server since 2008. The default and easiest mapping is to use the .Net datetime type for date values. The worst trouble with this is the conversion of dates when transmitted west over a time zone boundary. 2013-03-01 EST becomes 2013-02-28 11:00 PM CST. What is your favorite method of avoiding that problem?

      • Hi Bodhi,

        I’m not sure I understand your question 100%, but here’s an answer to what I think you might be asking:

        Your example might not be a problem if I wanted to store all the data in CST time– from that point of view it’s actually 2/28.

        If I wanted to store the data from the user’s local time perspective (but convert it to a date), I think I’d have two options– either grab the Datetime.Date property from the application tier (over in the local timezone, before it’s sent back to the SQL Server), or use a datetimeoffset and pass that back to the SQL Server (and then convert on the SQL Server end using the datetimeoffset data type in SQL Server as well). The first one of those sounds less computationally expensive.

        Disclaimer: I am in no way, shape or form a .NET expert. Or even pretending to be one.

        Hope this helps!
        Kendra

        • Thanks Kendra,
          I was unaware of the datetimeoffset type in .Net 3.5 and later versions. I like the solution you described.
          The problem is about columns like date-of-birth. We want to ignore the geographic relative offsets when referring to our date of birth. It is not intuitive to say that I was born in New York in the wee hours of the 10th but say it was the 9th when I am visiting Hawaii. Bank business dates are like that too. The transaction datetime may be 16:00 Friday afternoon but the posting business date may be the following Monday.
          I hope that .Net 5.0 will support a Date type.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php