1

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.

HerrimanCoder
  • 6,835
  • 24
  • 78
  • 158

1 Answers1

2

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))
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I think this isn't what He wants, It would be something like concatenate the date and the time to create the new start and end as datetime of course – Jorge Campos Jan 12 '16 at 04:33
  • Have a look [here](http://stackoverflow.com/questions/4125931/mysql-combining-date-and-time-column-into-a-time-stamp). It seems MySQL has a `TIMESTAMP` function which can do the heavy lifting, and I'd rather rely on this than formatting, the latter which is prone to error. – Tim Biegeleisen Jan 12 '16 at 04:37
  • @JorgeCampos could you post an answer with the sql using concat() and date_format()? I'd greatly appreciate it. – HerrimanCoder Jan 12 '16 at 04:51
  • @SweatCoder Tim already did it: comment above: http://stackoverflow.com/a/4125957/460557 – Jorge Campos Jan 12 '16 at 04:52
  • @TimBiegeleisen your update throws a syntax error: `Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'crt.EndTime = CONCAT(DATE(crs.WorkDate), ' ', TIME(crt.EndTime))' at line 5 0.000 sec` -- any ideas? – HerrimanCoder Jan 12 '16 at 05:04
  • Sorry there were commas missing. Try it again. You need to use `UPDATE...SET c1 = v1, c2 = v2` – Tim Biegeleisen Jan 12 '16 at 05:06
  • 1
    Bingo. Thanks so much Tim and Jorge. FYI: I used the second update, didn't try the first. – HerrimanCoder Jan 12 '16 at 05:08