Tag Archives: DATETIME Vs SMALLDATETIME

Difference between SMALLDATETIME and DATETIME Data Types in Sql Server

Both SMALLDATETIME and DATETIME Data Types in Sql Server are used for storing Date and Time values in Sql Server. Below table summarizes some of the major difference between these two Data Types.

[ALSO READ] DateTime vs DateTime2

SMALLDATETIME

DATETIME

FORMAT YYYY-MM-DD hh:mm:ss YYYY-MM-DD hh:mm:ss.nnn
MIN Value 1900-01-01 00:00:00 1753-01-01 00:00:00
MAX Value 2079-06-06 23:59:00 9999-12-31 23:59:59.997
Storage Size 4 bytes 8 bytes
Usage sql-datetime-accuracy-1 datetime-usage-example
Accuracy 1 Minute

Second’s values that are 29.998 seconds or less are rounded down to the nearest minute. And second’s values of 29.999 seconds or more are rounded up to the nearest minute. So seconds part value is always 00.

Example 1:
sql-smalldatetime-seconds-accuracy-1
Example 2:
sql-smalldatetime-seconds-accuracy-2

Rounded to increments of .000, .003, or .007 second
It means:

If time part in the date is 23:59:58.990 or 23:59:58.991, it will be stored as 23:59:58.990.

Example
sql-datetime-accuracy-1

If time part in the date is 23:59:58.992 or 23:59:58.993 or 23:59:58.994, it will rounded and stored as 23:59:58.993

Example 2:
sql-datetime-accuracy-2

If time part in the date is 23:59:58.995 or 23:59:58.996 or 23:59:58.997 or 23:59:58.998, it will be rounded and stored as 23:59:58.997

If time part in the date is 23:59:58.999, it will be rounded and stored as 23:59:59.000

ALSO READ