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.



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) 
(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)

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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 125 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.