将DB2中的整数转换为日期间隔，以便mysql插入

Question

I have a php script with this select query:

$plcQueryDB2 = " 

    select  
            cstnoc AS DEALER,
            framec AS FRAME,
            covr1c AS COVER,
            colr1c AS COLOR ,
            extd1d AS SHIPDATE
      FROM GPORPCFL
      group by invnoc,cstnoc, slsnoc, orqtyc, framec, covr1c,colr1c, extd1d
      limit 200
";

The result set is correct, but the issue is I'm using the result to insert into mysql and need the SHIPDATE column in true date format since it's an integer in DB2. It looks like 20180202 whereas I need 2018-02-02 for mysql.

The other factor is I need to use that one value (SHIPDATE) for 2 values on the insert.

I need to insert SHIPDATE + 7 DAYS for start_date and SHIPDATE + 127 DAYS for expire_date.

Here's the insert:

$stmt = $PDO->prepare("
        INSERT INTO placements (sku_id, group_id, dealer_id, start_date, expire_date)
        SELECT id, sku_group_id, :DEALER, :SHIPDATE + 7 day interval, :SHIPDATE + 127 day interval
        FROM skus s
        WHERE  s.frame=:FRAME AND s.cover1=:COVER AND s.color1=:COLOR
    ");

Obviously I get a mysql syntax error at : :SHIPDATE + 7 day interval, :SHIPDATE + 127 day interval

And that's still not putting it into true date format. So I have two issues revolving around the same field here.

What's the best way I can convert to true date format and add the intervals for this select or insert?

I can either ammend the select to convert and add the interval or do it at the insert level, I just don't really know how or which is best.

Answer 1

If your DB2 date is "$date_from_db2":

$newdate = date("Y-m-d", strtotime('$date_from_db2'));

That requires an intermediate step, of course.