SQL Query to Convert Datetime to Date
In MS SQL Server, dates are complicated for newbies, since while working with the database, the format of the date in the table must be matched with the input date in order to insert. In various scenarios instead of date, DateTime (time is also involved with date) is used. In this article, we will learn how to convert a DateTime to a DATE by using the three different functions.
- CAST( )
- CONVERT( )
- TRY_CONVERT( )
- Using Substring
The aim of this article data is to convert DateTime to Date in SQL Server like YYYY-MM-DD HH:MM: SS to YYYY-MM-DD.
Method 1: Using cast
This is a function for casting one type to another type, So here we will use for cast DateTime to date.
Syntax:
CAST( dateToConvert AS DATE)
Example 1:
Query:
SELECT CAST(GETDATE() AS DATE) AS CURRENT_DATE
Output:
GETDATE(): This function return current date time like(2021-08-27 17:26:36.710)
Example 2;
Query:
SELECT CAST('2021-08-27 17:26:36.710' AS DATE) AS CURRENT_DATE_GFG
Output:
Method 2: Using Convert
This is a function for convert one type to another type, So here we will use it to convert DateTime to date.
Syntax:
CONVERT(DATE, dateToConvert)
Example 1:
Query:
SELECT CONVERT(DATE, GETDATE()) AS CURRENT_DATE_GFG
Output:
Example 2:
Query:
SELECT CONVERT(DATE, '2021-08-27 17:26:36.710' ) AS CURRENT_DATE_GFG
Output:
Method 3: Try_Convert
This is a function for casting one type to another type, So here we will use for Convert DateTime to date. if the date is invalid then it will be null while Convert generates an error.
Syntax:
TRY_CONVERT(DATE, dateToConvert)
SELECT TRY_CONVERT(DATE,’2021-08-27 17:26:36.710′) AS CURRENT_DATE_GFG
Example 1:
Query:
SELECT TRY_CONVERT(DATE,GETDATE()) AS CURRENT_DATE_GFG
Output:
Example 2:
Query:
SELECT TRY_CONVERT(DATE,'2021-08-27 17:26:36.710') AS CURRENT_DATE_GFG
Output:
Method 4: Using Substring
This is a function to use get a short string or substring, so here use we get substring 0 to 11 index.
Syntax:
SUBSTRING( dateToConvert ,0,11)
Example 1:
Query:
SELECT SUBSTRING( '2021-08-27 17:26:36.710' ,0,11) AS CURRENT_DATE_GFG
Output:
Example 2;
Query:
SELECT SUBSTRING( CONVERT(varchar(17), GETDATE(), 23) ,0,11) AS CURRENT_DATE_GFG
Output:
Contact Us