Getting midnight of today

Database Administrators Asked by Logu on August 25, 2020

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.

4 Answers

With the MySQL functions CURDATE() and TIMESTAMP


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().


SQL commands are case insensitive.

Answered by Froggiz on August 25, 2020

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

Answered by harley81 on August 25, 2020

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)

Answered by til_b on August 25, 2020

The documentation surprisigly contains a solution:

SELECT timestamp(current_date);

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

Answered by dezso on August 25, 2020

Add your own answers!

Related Questions

HAPROXY for Postgres Failover Master/Replica

0  Asked on December 31, 2020 by jayson-gonzaga


Error 0xc000007b with Oracle 19c

2  Asked on December 28, 2020 by ben-a


Join to tables dynamically depending on data

2  Asked on December 28, 2020 by yellephen


PostgreSQL exits the shell when importing large data

2  Asked on December 26, 2020 by 3bdalla


Spike and time out errors every x th minute

1  Asked on December 25, 2020 by beginnerdba


Oracle RAC and DataGuard configuration

1  Asked on December 25, 2020 by yifan


Segragating First LSN and Last LSN from Output MySQL

0  Asked on December 24, 2020 by jabir-baig


Lossless Join Decomposition Criteria

1  Asked on December 23, 2020 by kevin-wu


Mongodb Upgrade from 1.8.2 to 4.4

0  Asked on December 23, 2020 by amanullah


SQLite database becomes corrupt

1  Asked on December 22, 2020 by jkozma


How do I export a multi table database with exceptions?

2  Asked on December 21, 2020 by soberuprising


MS SQL login periodically doesn’t work

2  Asked on December 20, 2020 by alonzo-hess


PostgreSQL: Find row with JSON object attribute in array

1  Asked on December 20, 2020 by karlsc


Hotel booking schema

1  Asked on December 20, 2020 by rakoun


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir