Skip to content
Related Articles

Related Articles

Improve Article

SQL Query to Convert Datetime to Date

  • Last Updated : 14 Sep, 2021

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:

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :