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 time, date, datetime2 and datetimeoffset data types for new work. They are SQL Standard compliant, portable, and has better range and precision.
Table of Contents
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 1⁄300 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
| DateTime | SmallDateTime | |
|---|---|---|
| Syntax | datetime | smalldatetime |
| Usage | CREATE TABLE Table1 ( Column1 datetime ) | CREATE TABLE Table1 ( Column1 smalldatetime ) |
| Date range | 1753-01-01 to 9999-12-31 | 1900-01-01 to 2079-06-06 |
| Time range | 00:00:00.000 through 23:59:59.997 | 00:00 through 23:59 |
| Storage size | 8 bytes | 4 bytes, fixed |
| Accuracy | Rounded to increments of .000, .003, or .007 seconds | One minute |
| Default value | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 |
DateTime Examples
Creating DateTime & SmallDateTime Columns
The following example shows how to Create Table with DateTime & SmallDateTime columns.
1 2 3 4 5 6 | CREATE TABLE Table1 ( Column1 datetime , Column2 SmallDateTime ) |
Inserting Values to DateTime & SmallDateTime Columns
1 2 3 4 5 6 7 8 9 10 | Insert into Table1 (Column1, Column2) Values ('2020-12-21 12:40:53','2020-12-20 12:40:53' ) Select * From Table1 ----------------------- ------------------- 2020-12-21 12:40:53.000 2020-12-20 12:41:00 |
Local Variables
1 2 3 4 5 6 7 8 9 | DECLARE @varDateTime datetime DECLARE @varSmalldatetime smalldatetime; SET @varDateTime = '2020-12-21 12:40:53'; SET @varSmalldatetime ='2020-12-21 12:40:53'; SELECT @varDateTime, @varSmalldatetime; |
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
1 2 3 4 5 6 7 8 9 | Insert into Table1 (Column1, Column2) Values ('2020-12-21','2020-12-21' ) Column1 Column1 ----------------------- -------------------- 2020-12-21 00:00:00.000 2020-12-21 00:00:00 |
And if no dates are provided, then the 1900-01-01 is used as the default date.
1 2 3 4 5 6 7 8 9 | Insert into Table1 (Column1, Column2) Values ('20:50:50.3','20:50:50.3' ) Column1 Column1 ----------------------- -------------------- 1900-01-01 20:50:50.300 1900-01-01 20:51:00 |
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
1 2 3 4 5 6 7 8 9 10 | Insert into Table1 (Column1, Column2) Values ('1753-01-01','1900-01-01' ) Select * from Table1 Column1 Column2 ----------------------- ------------------- 1753-01-01 00:00:00.000 1900-01-01 00:00:00 |
Why do you have DateTime range as 1753-01-01 to 1999-12-01 ??!!!
1999?? If you can’t be accurate, don’t post non-sense.
The guy just made a typo. I hope there will be less rude nonhumans like you Joe. I found smalldatetime is 1 minute accuracy so the article is good.