How to set time to 00:00:00 with GETDATE() in SQL

SQL date getdate time

In SQL Server, you can use the GETDATE() function to get the current date and time.

SELECT GETDATE();

This will return the current date and time in the following format: YYYY-MM-DD HH:MI:SS.

If you need just the date without the time, you can use:

1. Using CAST

You can cast the result of GETDATE() to a date type and then back to datetime type, which will automatically set the time to 00:00:00.

For example:

// sql time getdate date
SELECT CAST(CAST(GETDATE() AS date) AS datetime) //2024-10-07 00:00:00.000

2. Using CONVERT

You can use the CONVERT function similarly.

For example, sql getdate with 00 time

SELECT CONVERT(datetime, CONVERT(date, GETDATE())) //2024-10-07 00:00:00.000

3. Using DATEADD

If you want to explicitly set the time to midnight while keeping the date part from GETDATE(), you can use DATEADD.

For example:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) //2024-10-07 00:00:00.000

All the above methods will return the current date with the time set to 00:00:00. You can choose any of the methods above based on your preference.

They all effectively achieve the same result of getting the current date with the time portion set to midnight.