wmarbut wmarbut - 3 months ago 10
MySQL Question

MySQL modify datetime set hours and minutes without modifying month, year, and day

I have a database with a DateTime column and I would like to modify the hours and minutes without touching anything else. I can't just add an interval b/c the values all differ.

e.g

*************************************************
** What I've got *|* What I want **
************************|************************
** 2012-10-01 10:01:01 *|* 2012-10-01 23:59:59 **
** 2012-08-03 13:09:01 *|* 2012-08-03 23:59:59 **
** 2012-05-10 09:00:01 *|* 2012-05-10 23:59:59 **
** 2012-03-20 20:01:01 *|* 2012-03-20 23:59:59 **
*************************************************


Is there a way to do this from a query without having to involve another programming language?

Answer

Yes you can do this rather easily:

UPDATE tablename SET columnname = CONCAT_WS(' ', DATE(columname), '23:59:59')