Dates In SQL Server: DATETIME2 vs SMALLDATETIME

While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates, I touched on the datatypes and realised that it’s worth devoting some time to the subtle but potentially dangerous difference between these two.

I previously said:

If you need to include time (so not just the whole day) then use DATETIME2 or SMALLDATETIME (unless you need the backwards compatibility of DATETIME).

Most people look at SMALLDATETIME and see that it uses less storage (this can be very handy when looking at very large tables). In most cases now, disk space is cheap and the advantage of the space saving is negligible.

The key difference between the two that is often overlooked (dangerously), is that they round differently. DATETIME2 always rounds down (depending on it’s level of precision). SMALLDATETIME rounds to the closest. With SMALLDATETIME activity can get attributed to the next day/time period which may not be the desired result. The last record for Tuesday could become stored and managed as the first record for Wednesday! This can have a massive impact on a report or business process.

I have taken one of the samples mentioned in stack overflow here and built it in SQL Fiddle. the way the day can change with the SMALLDATETIME rounding.

Examples

--------------------------------------------------------------------------------
-- Create Table
--------------------------------------------------------------------------------
CREATE TABLE t
(
  sdt SMALLDATETIME,
  dt2 DATETIME2(2)
);

--------------------------------------------------------------------------------
-- Populate
--------------------------------------------------------------------------------
WITH date_time_values AS (
SELECT '2017-01-01T11:22:22.33' AS [Date_Value]
UNION ALL
SELECT '2017-01-01T11:22:33.33' AS [Date_Value]
UNION ALL
SELECT '2017-01-01T23:59:59.59' AS [Date_Value]
)
INSERT
  INTO t
SELECT Date_Value, Date_Value
  FROM date_time_values 
;

--------------------------------------------------------------------------------
-- View Conversions
--------------------------------------------------------------------------------
SELECT sdt               AS [ISO Date Time],
       dt2               AS DATETIME2,
       CAST(sdt AS DATE) AS SMALLDATETIME_DATE,
       CAST(dt2 AS DATE) AS DATETIME2_DATE
  FROM t
;

ISO Date Time	DATETIME2	SMALLDATETIME_DATE	DATETIME2_DATE
2017-01-01T11:22:00Z	2017-01-01 11:22:22.33	2017-01-01	2017-01-01
2017-01-01T11:23:00Z	2017-01-01 11:22:33.33	2017-01-01	2017-01-01
2017-01-02T00:00:00Z	2017-01-01 23:59:59.59	2017-01-02	2017-01-01

In the above, the last row shows how the end of one day can become the start of the next.

To see it in action, have a look at the SQL Fiddle – DateTime2 VS SMALLDATE TIME