Date data type allows you to store the date data in the database. It takes 3 bytes to store a date value.
SYNTAX
date
The format of date is YYY-MM-DD, where YYYY is four digits from 0001 to 9999 that represent a year. MM is two digits from 01 to 12 that represent a month in the specified year, and DD is two digits from 01 to 31, depending on the month, that represents a day of the specified month.
The default date is 1900-01-01.
The range of date value supported by date data type is 0001-01-01 through 9999-12-31.
Lets take a look at DATE data type in SQL Server.
Following example demonstrate how to store a date value into date data type variable and then display.
DECLARE @date date= '2018-11-30'; SELECT @date AS 'date'
You can see, first we store the date value to variable @date which is of date data type and the date value is ‘2018-11-30’ which has the format as YYY-MM-DD.
After that we select the variable @date which returns the date value.
Lets take one more example, now we will create a table named datedemo which contains a date column as shown below.
CREATE TABLE dbo.datedemo (Id INT, DateCol DATE )
Now you can see table is created but do not have any record yet.
SELECT * FROM dbo.datedemo
Lets see how to insert a date value into table. When you insert a date value in table, value should be enclosed with single quote.
INSERT INTO dbo.datedemo (Id, DateCol) Values (1, '2018-10-23'), (2, '2018-09-22'), (3, '2017-07-25')
Once you insert the records into table, lets fetch the records from table.
You can see the records are inserted into table, and you can also see date values there in column Datecol.
Lets see the other valid date string format supported by Date data type in SQL Server.
Format: MM/DD/YYY
Lets insert a date value ’12/31/2019′ in table in format, mm/dd/yyy.
INSERT INTO dbo.datedemo (Id, DateCol) Values (4,'12/31/2019')
As you can see, record is inserted. Lets check the table.
You can see date value you inserted in table is there. That means format MM/DD/YYYY is also a valid date format that is supported by Date data type.
SELECT * FROM dbo.datedemo
Similarly, you can also explore other format as well : Refer microsoft documentation on Date datatype .
Also Read..
