This article shows you how to convert DATETIME to DATE using Conversion Functions(Transact-SQL).
In SQL Server, you can convert a datetime data type to a date data type using various functions and techniques.

The DATETIME data type stores both date and time information, while the DATE data type stores only the date portion. Converting a datetime to a date can be useful when you want to work with date information only and ignore the time component. Here are some methods to achieve this conversion:

Using CAST or CONVERT Function

You can convert a Datetime data type to a Date data type using the CONVERT function or the CAST function.
CAST(): Ideal for straightforward type conversions where no special formatting or regional considerations are required. For example, converting an integer to a decimal.
CONVERT(): Best suited for cases where you need to format date and time values, or when you want to handle localization and regional-specific formatting, like converting a date to a specific style.
Here's an example:

SELECT CAST(GETDATE() AS date) AS MyDate;
DECLARE @varchar_date varchar(50);
SET @varchar_date = '2023-10-17 08:03:10'
SELECT CONVERT(date, @varchar_date)

Using the DATEADD Function

Another way to extract the date portion from a datetime is by using the DATEADD function. This method essentially resets the time portion to midnight:

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS DateOnly;

The DATEDIFF function calculates the number of days between the date 0 (which represents '1900-01-01') and the original datetime. The DATEADD function then adds this difference to the date 0, effectively removing the time component.

Using the FORMAT Function

If you're using SQL Server 2012 or later, you can use the FORMAT function to convert a datetime to a string in the desired format, and then cast it back to a date:

SELECT CAST(FORMAT(GETDATE(), 'yyyy-MM-dd') AS date) AS DateFormat;

This method gives you more control over the output date format if needed.

Using the DATEFROMPARTS Function

The DATEFROMPARTS function allows you to create a date value by specifying the year, month, and day components. You can extract these components from a datetime to create a date:

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())) AS DateParts;

This method explicitly constructs a date based on the year, month, and day from the original datetime.

Choose the method that best suits your needs and the version of SQL Server you are using. Converting datetime to date is a common operation when working with SQL data, and it allows you to focus on date-related queries and calculations.