MySQL: Get the datetime field with time part reset to midnight (00:00:00)?-CodePudding

I am trying to query the PurchaseDate datetime column from tmp table along with a custom column that is PurchaseDate column with time as 00:00:00 / midnight:

PurchaseDate	StartOfDay
1996-07-16 20:00:00	1996-07-16 00:00:00
1996-07-10 21:19:00	1996-07-10 00:00:00
1996-07-12 22:18:00	1996-07-12 00:00:00

I wasn't able to do it using DATEDIFF(). How can I do it in the simplest way?

As suggested, I have tried using date(). However that converts the column type to date and I would like to retain it as a datetime.

CodePudding user response：

Convert to date to truncate the time part, then cast to datetime:

select
    PurchaseDate,
    cast(date(PurchaseDate) as datetime) as StartOfDay
from mytable

See live demo.