DateTime & SmallDateTime in SQL Server

The DateTime & SmallDateTime in SQL Server are the data types that store both date & time together. The time is based on the 24 hours clock.

The Microsoft advices users not to use DateTime & SmallDateTime Data Types. Instead use the timedatedatetime2 and datetimeoffset data types for new work. They are SQL Standard compliant, portable, and has better range and precision.

DateTime in SQL Server

The DateTime data type stores both date & time. The Datetime stores the number of clock ticks after midnight. Each clock tick is 1300 of a second. While displaying the time it uses the format hh:mm:ss[.nnn].

SmallDateTime in SQL Server

The SmallDateTime data types also stores both date & time together, But it stores the time only up to minute. it does not store the seconds.

If you try to insert seconds, values up to 29.998 it is rounded down to the nearest minute. Values above 29.999 seconds are rounded up.

DateTime Vs SmallDateTime

The following table shows the differences between DateTime & SmallDateTime

 DateTimeSmallDateTime
Syntaxdatetimesmalldatetime
UsageCREATE TABLE Table1 ( Column1 datetime )CREATE TABLE Table1 ( Column1 smalldatetime )
Date range1753-01-01 to 1999-12-011900-01-01 to 2079-06-06
Time range00:00:00.000 through 23:59:59.99700:00 through 23:59
Storage size8 bytes4 bytes, fixed
AccuracyRounded to increments of .000, .003, or .007 secondsOne minute
Default value1900-01-01 00:00:001900-01-01 00:00:00

DateTime Examples

Creating DateTime & SmallDateTime Columns

The following example shows how to Create Table with DateTime & SmallDateTime columns.

Inserting Values to DateTime & SmallDateTime Columns

Local Variables

Default Date & Time

Both DateTime & SmallDateTime always contain date and a time.

If you do not provide the time, then it defaults to 00:00:00

And if no dates are provided, then the 1900-01-01 is used as the default date.

Range

Both these data types are limited in Range. The Minimum that you can insert into a DateTime is 1753-01-01 & to the SmallDateTime is 1900-01-01

Reference

Leave a Comment

Your email address will not be published.

Scroll to Top