CODEWITHSUNDEEP
mysqlsql
HerrimanCoder
HerrimanCoder

Reputation: 7218

Change date while preserving time

I need to update a bunch of dates in a MySQL table while preserving the time portion of the date. For example, I join like this:

select crt.StartTime, crt.EndTime, crs.WorkDate
from CrewReportTimesheet crt
join CrewReportSummary crs
on crt.CrewReportSummaryID = crs.CrewReportSummaryID

crt.StartTime and crt.EndTime have wrong dates but correct times. crs.WorkDate has the right date but no times. So for all records that match the above join, I need to set crt.StartTime and crt.EndTime to the same date as crs.WorkDate while preserving the times in those columns.

Here's an example of the bad data:

enter image description here

Notice the first 2 columns with timestamps. Time is right, date is wrong. Cells A1 and B1 should be 2015-12-19 just like C1. And so on.

What is the best way to do this? Thanks in advance.

Upvotes: 1

Views: 59

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You can use an UPDATE statement along with an INNER JOIN to choose the records you want to update.

UPDATE CrewReportTimesheet crt
    INNER JOIN CrewReportSummary crs
    ON crt.CrewReportSummaryID = crs.CrewReportSummaryID
SET crt.StartTime = TIMESTAMP(DATE(crs.WorkDate), TIME(crt.StartTime)),
    crt.EndTime   = TIMESTAMP(DATE(crs.WorkDate), TIME(crt.EndTime))

If you want to use CONCAT() instead you can try the following:

UPDATE CrewReportTimesheet crt
    INNER JOIN CrewReportSummary crs
    ON crt.CrewReportSummaryID = crs.CrewReportSummaryID
SET crt.StartTime = CONCAT(DATE(crs.WorkDate), ' ', TIME(crt.StartTime)),
    crt.EndTime   = CONCAT(DATE(crs.WorkDate), ' ', TIME(crt.EndTime))

Upvotes: 2

Related Questions