Date is the SQL Server data type that we use to store Date. It stores the Date without time & without time zone information.
Table of Contents
How SQL Server stores date
The SQL Server stores both date & time as integers. The date integer stores the no of days elapsed since a base date (which is 1900-01-01).
Since Date is internally a integer, we can convert any integer to date using the cast function. Convert 0 to date will give you the base date.
1 2 3 4 5 6 | select cast(0 as datetime) ***Result 1900-01-01 00:00:00.000 |
Similarly, you can convert the current date to int. The GETDATE() method returns the current date
1 2 3 4 5 6 7 8 9 10 11 12 | select cast(GETDATE() as int) ****Result 44177 select cast(44177 as datetime) ***Result 2020-12-14 00:00:00.000 |
Date in SQL Server
Use Date Data type to create a Date column/variable without any time information. The time defaults to 00:00:00 and sql server will not store it in the Database. Hence it is very efficient way to store date, when there is no need to store time.
The Syntax for creating a table column with date is as follows
1 2 3 | CREATE TABLE TestDate ( Col1 date ) |
Inserting Date Values
1 2 3 4 5 6 7 8 9 10 | Insert into TestDate (col1) values ( '2020-12-15') select * from TestDate col1 ---------- 2020-12-15 |
Storage size & Range
| Min Date | 0001-01-01 |
|---|---|
| Max Date | 9999-12-31 |
| Storage | 3 Bytes |
| Default Value | 1900-01-01 |
| Default string literal format | YYYY-MM-DD |
Displaying the Date
The date is stored as number and is independent of any formats.
SQL Server by default uses the YYYY-MM-DD to display the date. You can customize that using the format function. The article on SQL Server date formats lists the various date formats and how to convert them.
You can change the default language in SQL Server, which also changes the display format of the date.
Format for Inserting the date
While you can display the date in any formats, you need to be careful while inserting the date in the database.
This is because, the date 02/03/2020 can be interpreted as 2nd march or 3rd February depending on where you live.
YYYYMMDD Universal format
Always use the format YYYYMMDD to insert the date into database. This is the default format that SQL Server uses. It is also the safe format and can be interpreted only in one way.
1 2 3 4 5 6 7 8 | //YYYY-MM-DD// Insert into TestDate (col1) values ( '2020-12-15') Insert into TestDate (col1) values ( '2020.12.15') Insert into TestDate (col1) values ( '2020/12/15') Insert into TestDate (col1) values ( '20201215') Insert into TestDate (col1) values ( '2020-5-2') |
The following query will run without error irrespective of the language or DATEFORMAT used by the SQL Server
Language Settings
The language settings also determines the date formats, that you can use to insert the date values.
To find out the current language, run the TSQL command and note down the language.
1 2 3 | select @@language; |
Next, run the exec sp_helplanguage to procedure. Look for the value of dateformat column.
1 2 3 | exec sp_helplanguage |
The format may one of dmy, mdy & ymd.
IF format is dmy, then you can insert the value 12-15-2020. But if the format is mdy it will result in an error.
Using DATEFORMAT
You can also use the DATEFORMAT to set the format to dmy, mdy & ymd. The setting will work only for the current session. When a new session starts the DATEFORMAT revert back to the default.
1 2 3 4 5 6 7 8 9 | SET DATEFORMAT 'mdy' Insert into TestDate (col1) values ( '12-15-2020') Insert into TestDate (col1) values ( '12/15/2020') Insert into TestDate (col1) values ( '12.15.2020') Insert into TestDate (col1) values ( '5.2.2020') |
Two digit year
The year part can be specified either as four digit or two-digit years. Use four-digit years whenever possible.
For two digits year, the cut of year is 2049 and the time span is from 1950 to 2049.
Hence the years from 50 to 99 is treated as 1950 to 1999. And the year from 00 to 49 is treated as 2000 to 2049.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Insert into TestDate (col1) values ( '70-12-01') Insert into TestDate (col1) values ( '49-12-01') Insert into TestDate (col1) values ( '50-12-01') Insert into TestDate (col1) values ( '01-12-01') select * from TestDate col1 ---------- 1970-12-01 2049-12-01 1950-12-01 2001-12-01 |
You can change the cutoff year using the two digit year cutoff option
Separators
You can use the dash marks (/), hyphens (-), or periods (.) as separators.
