How to convert datetime to date (with time set to 00:00:00.000) in SQL

If you want to convert a string like '2024-10-07 08:50:12.000' to a DATETIME in SQL Server while ignoring the time and setting it to 00:00:00.000, you can use the CAST or CONVERT functions to extract just the date part and set the time to midnight.

SELECT CAST(CONVERT(VARCHAR(10),'2024-10-07 08:50:12.000', 101) AS DATETIME) //2024-10-07 00:00:00.000

Syntax

SELECT CAST(your_datetime_column AS DATE) AS date_only
FROM your_table

For example:

SELECT CAST(CreatedDate AS DATE) AS CreatedDate
FROM Products

SELECT CAST(CONVERT(VARCHAR, GETDATE(), 102) AS DATETIME) //2024-10-07 00:00:00.000

SELECT CONVERT(CHAR(10), CAST('2024-10-07 08:50:12.000' AS DATETIME), 101) //10/07/2024

SELECT CONVERT(VARCHAR, CONVERT (DATETIME, '2024-10-07 08:50:12.000'), 101) //10/07/2024