The range of the date data type, as defined in Transact-SQL (used in SQL Server), is from January 1, 0001, through December 31, 9999.
This data type stores only the date part (year, month, and day) without the time component. Its fixed range allows it to represent any calendar date within that millennium span.
Here's a look at the ranges for several date and time data types in Transact-SQL, based on the provided reference:
| Data type | Format | Range |
|---|---|---|
| date | yyyy-MM-dd | 0001-01-01 through 9999-12-31 |
| smalldatetime | yyyy-MM-dd HH:mm:ss | 1900-01-01 through 2079-06-06 |
| datetime | yyyy-MM-dd HH:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 |
| datetime2 | yyyy-MM-dd HH:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 |
As highlighted in the table, the date data type provides the widest range for just the date component, covering dates from the year 1 to the year 9999.
This information is consistent with the official Microsoft documentation on Date and Time Data Types and Functions (Transact-SQL).
