Get Date From Datetime in SQL Server

In the previous article, I mentioned SQL server date format and converting it (Various examples) but now in this article, we will talk about getting only DATE from Datetime in SQL Server. To convert a datetime to date, you can use the CONVERT(), TRY_CONVERT(), or CAST() function.

Get Date from datetime using Convert()

First, we will see how to get the only date from datetime using Convert() Method, which is easiet.

So, here is the usual Syntax of Convert()

Select Convert(date, date_time_value)

Where date = format to get output and

date_time_value = Datetime value from which we need date only.

If the above conversion fails, Convert() will throw error.

Example:

SELECT CONVERT(date, GETDATE())

GETDATE() will return today's date and time, to get only DATE from it, I have used Convert() function.

If you want to change the format of above date, you can use it as below

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) --2022/06/22  (111- gives output in yyyy/MM/dd)

Using TRY_CONVERT()

TRY_CONVERT() can also be used to convert the datetime to a date, only difference between CONVERT() and TRY_CONVERT() is that TRY_CONVERT() return NULL if conversion fails.

Syntax

Select TRY_CONVERT(date, date_time_value)

Where date = format to get output and

date_time_value = Datetime value from which we need date only.

Example:

SELECT TRY_CONVERT(date, GETDATE())

Output:

2022-06-22 --today's date in yyyy-mm-dd format

Using Cast()

CAST does the same job as CONVERT() and TRY_CONVERT() to get date from datetime, here is it's syntax

CAST(datetime_expression AS DATE)

Where date_time_value = Datetime value from which we need date only.

Example:

SELECT CAST(GETDATE() AS DATE)

Output:

2022-06-22 --today's date in yyyy-mm-dd format

The CAST function is ANSI standard and portable as it is compatible to use in other databases WHILE the CONVERT function is a specific function of the SQL server.

Get Date from Datetime column in SQL Server

You can simply use CAST and get Datetime column value date part only using SQL Server

DECLARE @LastChangeDate as datetime --create datetime variable
SET @LastChangeDate = GetDate()

Select @LastChangeDate --before cast

SELECT CAST(@LastChangeDate AS DATE) -- after cast

Output:

That's it.

You may also like to read:

SQL server date format and converting it (Various examples)

SQL Server Management Studio (SSMS) Alternatives (Mac, Windows, Linux)

Microsoft SQL Server Versions List

Understanding SQL server switch case (With Example)

How to backup SQL Server database ? (Various Ways explained)

Convert Month Number to Month Name in SQL

Free SQL Server Reporting Tools