Getting midnight of today

Question

I want to get 00:00:00 of a day.  Should be done without using CONCAT. What options are there?

We can use CURDATE() function in select query but need to display like 2014-05-05 00:00:00. If NOW() will give like 2014-05-05 12:05:37.

I want add default value 00:00:00 with CURDATE() in query.

Froggiz · Answer

With the  MySQL functions CURDATE() and TIMESTAMP
SELECT TIMESTAMP(CURDATE());

it is the same result as
SELECT timestamp(current_date());

or even
SELECT timestamp(current_date);

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

Note:
SQL commands are case insensitive.

harley81 · Answer

If you have a date like 2014-05-05 12:05:37 you can use DATE_FORMAT.

DATE_FORMAT("2014-05-05 12:05:37", "%Y-%m-%d 00:00:00")

It returns 2014-05-05 00:00:00

til_b · Answer

Another solution would be: cast getdate() to a date, and that back to a datetime.

Code (for MS SQL Server, but the idea applies also to mysql):

select cast(cast(GETDATE() as date) as datetime2)

dezso · Answer

The documentation surprisigly contains a solution:

SELECT timestamp(current_date);

(which is basically the same as a comment above).

4 Answers