SQL DATE Data Type

The DATE data type specifies a date in SQL Server.

DATE supports dates from 0001-01-01 through 9999-12-31.

The default format is YYYY-MM-DD. The default value is 1900-01-01.

Example

#

A table with a DATE column.

CREATE TABLE DemoTable  
( 
  Id INT IDENTITY, 
  Holiday VARCHAR(100),
  HolidayDate DATE
);
GO  

INSERT INTO DemoTable VALUES ('Christmas', '2021-12-25');  
INSERT INTO DemoTable VALUES ('New Year', '12/31/2021');  
INSERT INTO DemoTable VALUES ('Valentine’s Day', '2021.02.14');
INSERT INTO DemoTable VALUES ('Halloween', '2021/10/31'); 
INSERT INTO DemoTable VALUES ('Easter', '2021-4-4'); 
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO
Results:  5 records
Id Holiday HolidayDate
1 Christmas 2021-12-25
2 New Year 2021-12-31
3 Valentine’s Day 2021-02-14
4 Halloween 2021-10-31
5 Easter 2021-04-04

Notice how SQL Server accepts different input date formats.

These formats are converted to the internal format which is 'YYYY-MM-DD'.

More Examples

DATE with CAST DATETIME VALUE

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all orders. Display the order dates as date (i.e. without time).
SELECT OrderNumber, FirstName, LastName, 
       CAST(OrderDate AS DATE) AS Date,
	   TotalAmount
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
OrderNumber FirstName LastName Date TotalAmount
542378 Paul Henriot 2012-07-04 440.00
542379 Karin Josephs 2012-07-05 1863.40
542380 Mario Pontes 2012-07-08 1813.00
542381 Mary Saveley 2012-07-08 670.80
542382 Pascale Cartrain 2012-07-09 3730.00

DATE with OTHER DATE AND TIME TYPES

This example displays date values as different SQL Server date and time types.
CREATE TABLE DemoTable  
( 
  MyDate DATE,
  MyTime TIME,
  MySmallDateTime SMALLDATETIME,
  MyDateTime DATETIME,
  MyDateTime2 DATETIME2,
  MyDateTimeOffset DATETIMEOFFSET
);
GO  

INSERT INTO DemoTable VALUES ('2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55',
                              '2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55',
                              '2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55');  
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO
Result:  1 record
MyDate MyTime MySmallDateTime MyDateTime MyDateTime2 MyDateTimeOffset
2021-10-22 13:54:19.0550000 2021-10-22 13:54:00 2021-10-22 13:54:19.057 2021-10-22 13:54:19.0550000 2021-10-22 13:54:19.0550000 +00:00

You may also like



Guides


vsn 3.1