Open In App

SQL Query to Convert Datetime to Date

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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.

 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:


Last Updated : 14 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads