Converting a datetime to a date value is a common requirement.
Three functions are available: CONVERT, TRY_CONVERT, and CAST.
This example converts the current datetime to a date.
SELECT CONVERT(DATE, GETDATE()) AS Date
| Date |
|---|
| 2023-12-21 |
GETDATE returns the current database server's datetime.
CONVERT converts the datetime value to a date format.
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 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.
SELECT GETDATE() AS 'DATETIME',
CONVERT(DATE, GETDATE()) AS 'CONVERT',
TRY_CONVERT(DATE, GETDATE()) AS 'TRY_CONVERT',
CAST(GETDATE() AS DATE) AS 'CAST'
| DATETIME | CONVERT | TRY_CONVERT | CAST |
|---|---|---|---|
| 2023-12-21 16:23:58.383 | 2023-12-21 | 2023-12-21 | 2023-12-21 |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
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
| 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 |
 |
|||
