SQL Server Date

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..

Date time Conversion

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 1,503 total views,  1 views today