MySQL DATETIME - Change only the date, MySQL

MySQL DATETIME - Change only the date

DROP TABLE IF EXISTS attendance;

CREATE TABLE attendance (id INT, punch_time DATETIME);

INSERT INTO attendance (id, punch_time) VALUES (1, '2019-02-15 19:06:15');
INSERT INTO attendance (id, punch_time) VALUES (2, '2019-02-15 17:12:23');
INSERT INTO attendance (id, punch_time) VALUES (3, '2019-02-15 23:00:03');
INSERT INTO attendance (id, punch_time) VALUES (4, '2019-02-15 20:33:56');
INSERT INTO attendance (id, punch_time) VALUES (5, '2019-02-15 18:19:31');
INSERT INTO attendance (id, punch_time) VALUES (6, '2019-02-15 16:43:47');
INSERT INTO attendance (id, punch_time) VALUES (7, '2019-02-16 00:19:31');
INSERT INTO attendance (id, punch_time) VALUES (8, '2019-02-16 00:43:47');

SELECT punch_time FROM attendance;

SELECT * FROM `attendance` WHERE `punch_time` BETWEEN '2019-02-15 06:30:00' AND '2019-02-15 23:59:59';

/*UPDATE attendance SET punch_time = DATE_ADD(punch_time, INTERVAL 15 - HOUR(punch_time) HOUR);*/
UPDATE attendance SET punch_time = concat('2019-03-25 ', time(punch_time)) WHERE `punch_time` BETWEEN '2019-02-15 06:30:00' AND '2019-02-15 23:59:59';

UPDATE attendance SET punch_time = concat('2019-03-26 ', time(punch_time)) WHERE `punch_time` BETWEEN '2019-02-16 00:00:01' AND '2019-02-16 01:00:00';

SELECT * FROM `attendance` WHERE `punch_time` BETWEEN '2019-02-15 06:30:00' AND '2019-02-15 23:59:59';

SELECT * FROM `attendance` WHERE `punch_time` BETWEEN '2019-02-16 00:00:01' AND '2019-02-16 01:00:00';

SELECT punch_time FROM attendance;


run \| edit \| history \| help	0

Please log in to post a comment.

Advertise here.