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:

References

Where to Start In Books Online

• Using Date and Time Data: http://msdn.microsoft.com/en-us/library/ms180878.aspx<
• Date and Time Functions: http://msdn.microsoft.com/en-us/library/ms186724.aspx
• Row Compression Implementation (contains storage sizes) http://msdn.microsoft.com/en-us/library/cc280576.aspx

Some of Kendra’s Posts

• Date Rounding Tactics and the Tiny Devil of SMALLDATETIME:http://www.littlekendra.com/2011/01/06/daterounding/
• Filling in Data Potholes Redux: Tally Tables vs CTEs: http://www.littlekendra.com/2011/01/04/tallytables/

Helpful Blog Links

• Aaron Bertrand shows you how the ‘Between’ operator can be tricky- particularly when working with dates and times: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx
• Aaron Bertrand describes many of the pitfalls and performance problems encountered when working with dates and times, with a special focus on querying ranges of dates: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx
• Aaron Bertrand asks, “Will you use EOMONTH?”: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/09/22/sql-server-v-next-denali-will-you-use-eomonth.aspx
• Itzik Ben-Gan gives a helpful summary of new functions in Denal (many of which are date related): http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/denali-tsql-glance-enhanced-functions-140785
• The ultimate guide to datetime datatypes (recently updated for 2008) by Tibor Karaszi http://www.karaszi.com/SQLServer/info_datetime.asp
• Internal storage of Datetime2 and Datetime offset by Peter Larsson: http://weblogs.sqlteam.com/peterl/archive/2010/12/15/the-internal-storage-of-a-datetime2-value.aspx
• Robyn Page’s DATETIME workbench: http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/
• Craig Freedman’s Blog on Implicit Conversions: http://blogs.msdn.com/b/craigfr/archive/2008/06/05/implicit-conversions.aspx
• PowerPivot(PRO) Date and DateTime- Sneaky Data Types! http://powerpivotpro.com/2011/02/21/date-and-datetime-sneaky-data-types/
• The Query Optimizer and Date Correlation Optimization by Fabiano Amorim http://www.simple-talk.com/sql/t-sql-programming/the-query-optimizer-date-correlation-optimisation/