Dates and Times in SQL Server

Dates and Times in SQL Server

Thanks for attending– or being curious about– Kendra’s SQL PASS session on Dates and Times in SQL Server. This page is your jumping off point to learn more.

Why Date? Why Time? Why DATETIME2(0)?

When working with clients creating new databases in SQL Server 2008 and higher, I find that developers rarely use new types available for date and time data. I created this presentation to compare the date and time types and to highlight “gotchas” and problems we encounter when working with temporal data in SQL Server.

Date and Time Data Types

My basic guidelines for selecting types are:

  • Consider the newer DATETIME2, DATE, and TIME types for your schema.
  • Keep only the precision you need, and keep your schema as lean as possible.
  • Be consistent with types within a database. If you want to use new types, carefully plan and execute a change for existing types.
  • If you use SMALLDATETIME, make sure you understand how it rounds (30 + seconds after will round up to the next minute), and the impact that may have on your data. (This impacts you both when you are selecting schema for permanent tables, and when you are declaring schema in temporary objects, and using a data type programmatically.)

What does “Not Recommended for New Development” Mean?

The Books Online entries from SMALLDATETIME and DATETIME in SQL Server both recommend using the newer types (such as DATE and DATETIME2) for “new work”. This does NOT mean that the older types are deprecated and/or won’t be supported.

The reasons cited in Books Online are for code portability. I personally find this reasoning persuasive– where SQL Server allows us to use types that are closer to standards agreed upon between database vendors, using those types can have benefits. If you’re in a situation where you’re creating software that can run on different types of RDBMS, you want to use types where if the same data were imported into each system, it would be as close to identical as possible after import, right?

There are other reasons to consider the new types as well. The DATE type is a mere 3 bytes per row, and the DATETIME2 type has an attractive configurable precision: if you don’t need fractional seconds, DATETIME2(0) is 6 bytes compared to the 8 bytes of DATETIME.

Does this mean we should never use DATETIME or SMALLDATETIME again? Nope– there will still be situations where these are appropriate. However, when choosing types, make sure you think hard about your options and consider the new types.

In other words, don’t just select between the two types you’ve been using for a while. Look at the accuracy of the data coming in and the business requirements you need to meet, and choose from ALL the existing types based on those two factors.

Rounding Date and Time Values

If I need to aggregate and round for a report like “show me all the transactions by minute for the last hour”, I prefer to use the date and time functions which are based on math. My testing shows these are much faster than rounding methods based on datepart or convert. DATEADD and DATEDIFF are speedy!

My favorite rounding formula is:

This example rounds a date to the nearest minute by essentially subtracting the number of minutes since the beginning of time (0), and then adding them back. This math turns out to be blazing fast in SQL Server.

Note that if you’re rounding to the year or month alone, you may have good reason to use a ‘calendar’ or ‘dates’ table to help you out. This is particularly useful because it allows you to use more indexes and filter early on.

Languages and Format

Use a language neutral combination of datetime type and format. This means you’ll always get the date/time value you’re expecting, even if your code is running in a session with different language settings than were in place when you write and tested your code. Neutrality simplifies your life in the long run.

Consider using the ISO 8601 format for production code:


Where to Start In Books Online

Some of Kendra’s Posts

Helpful Blog Links