MS Access [VBA/SQL] change time in date/time field only-CodePudding

I would like to update the time portion of every record to 00:00 so that I can group by day. So for example 01-Apr-2022 4:18:00 PM would become 01-Apr-2022 00:00.

I don't mind if this is SQL or VBA - whatever is more efficient & gets the job done.

Thanks!

CodePudding user response：

Solved by using DateValue in GROUP BY.

CodePudding user response：

If your date is stored like yyyy-mm-dd hh:mm:ss:

update TABLE set table_date = (select convert(varchar, table_date, 23) from TABLE where id = table_id) ' 00:00:00' where id = table_id

The idea is to get the first part of your date yyyy-mm-dd and concatenate with 00:00:00.