SQL Convert DATETIME to DATE

Converting a datetime to a date value is a common requirement in database solutions.

Three functions are available: CONVERT, TRY_CONVERT, and CAST.

Example

#

This example converts the current datetime to a date.

SELECT CONVERT(DATE, GETDATE()) AS Date
Result:  1 record
Date
2021-12-05

GETDATE returns the current database server's datetime.

CONVERT converts the datetime value to a date format.


Converting Datetime to Date

A datetime includes both date and time whereas date only includes the date.

The CONVERT function can be used to convert datetime to date values.

The TRY_CONVERT will do the same.

The CAST function can also convert datetime to date.


Syntax

Syntax for the CONVERT, TRY_CONVERT, and CAST functions.

Syntax of CONVERT.

CONVERT(date, datetime-value)

date -- indicates that the specified datetime-value will be converted to date.

datetime-value -- the datetime value to be converted.

Syntax of TRY_CONVERT.

TRY_CONVERT(date, datetime-value)

date -- indicates that the specified datetime-value will be converted to date.

datetime-value -- the datetime value to be converted.

TRY_CONVERT returns NULL if a conversion error occurs.

Syntax of the CAST function.

CAST(datetime-value AS DATE)

datetime-value -- the datetime value to be cast.

DATE -- indicates that the specified datetime-value will be cast to date.


More Examples

DATETIME to DATE with Different Methods

Convert the current datetime to date using different conversion methods.
SELECT GETDATE() AS 'DATETIME',
       CONVERT(DATE, GETDATE()) AS 'CONVERT',
       TRY_CONVERT(DATE, GETDATE()) AS 'TRY_CONVERT',
       CAST(GETDATE() AS DATE) AS 'CAST'
Result:  1 record
DATETIME CONVERT TRY_CONVERT CAST
2021-12-05 02:29:15.352 2021-12-05 2021-12-05 2021-12-05

DATETIME to DATE with COLUMN values

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List all the orders from January 2013.
SELECT FirstName, LastName,
       CONVERT(Date, OrderDate) AS Date,
       TotalAmount
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
 WHERE YEAR(OrderDate) = 2013 AND
       MONTH(OrderDate) = 1
Result:  33 records
FirstName LastName Date TotalAmount
Ann Devon 2013-01-01 3063.00
Paula Wilson 2013-01-01 3868.60
Roland Mendel 2013-01-02 2713.50
Roland Mendel 2013-01-03 1005.90
Giovanni Rovelli 2013-01-03 1675.00

You may also like



Guides


vsn 3.1