db2 query and date comparison in PHP

[thenorman138]

I'm trying to alter a db2 query in my php script and it's not executing properly when doing so. 

 

In the last line of my WHERE clause for the DB2 select, I'm using this:

 

AND date(substr(extd2d,1,4)||'-'||substr(EXTD2d,5,2)||'-'||substr(EXTD2d,7,2)) >= {$row['start_date']}

 

but getting an error that >= is not a valid comparison operand. However, if I remove {$row['start_date']} and use current_date or current_date - 100 DAY, it works perfectly.

 

To clarify, the value for 'start_date' in mysql is DATE data type and the value 'extd2d' in db2 is a packed field value that holds a date (20180202), so I'm converting it to try and match the YYYY-MM-DD format.

 

Why can't I get this to work as it is?

$sql = "
SELECT 
sku_id,
    dealer_id,
    locations,
    s.sku_group_id as groupID,
    s.frame as frame,
    s.cover1 as cover,
    s.color1 as color,
    start_date - interval 7 day as start_date
from placements p
inner join skus s on p.sku_id = s.id
where curdate() between p.start_date and p.expire_date
group by sku_id, dealer_id
limit 100";


$result = mysqli_query($conn,$sql);


while($row = mysqli_fetch_assoc($result)) 


{
    $resultData[] = $row;


$sql2 = "
SELECT
      framec,
      covr1c,
      colr1c,
      date(substr(extd2d,1,4)||'-'||substr(EXTD2d,5,2)||'-'||substr(EXTD2d,7,2))  as start_date,
      sum(orqtyc) as TotalQTY
  from table1
    where cstnoc = {$row['dealer_id']}
    AND framec = {$row['frame']}
      AND colr1c = {$row['color']}
      AND covr1c =  {$row['cover']}
      AND date(substr(extd2d,1,4)||'-'||substr(EXTD2d,5,2)||'-'||substr(EXTD2d,7,2)) >= {$row['start_date']}  /*This is the line in question*/
group by framec,covr1c,colr1c,extd2d
";


$result2 = odbc_exec($DB2Conn, $sql2);


while($row2 = odbc_fetch_array($result2)){


$db2Result[] = $row2;
}
}


print_r($resultData);
print_r($db2Result);

Edited April 8, 2018 by thenorman138

[requinix]

If the start_date is a string value then you have to treat it like a string value. And what do strings need...?

[thenorman138]

I thought packed field was like a decimal though?

 I don't know much about DB2 structures

[thenorman138]

But I'm also not sure what the difference would be with me using current_date and then a variable that holds a DATE data type

[requinix]

Table columns and data can have a variety of different types, but there are only a couple ways that data ever gets represented: as a number or a string. There are occasional exceptions, but for the most part things like INTs and DECIMALs and FLOATs and such are represented as numbers, while VARCHARs and DATEs and GUIDs and most everything else are represented as strings. Strings with quotes.

 

So if you want a date value in your query then you need to pretend it's a string.

 

But this

AND date(substr(extd2d,1,4)||'-'||substr(EXTD2d,5,2)||'-'||substr(EXTD2d,7,2)) >= {$row['start_date']}

concerns me. Why isn't extd2d already a DATE?

[thenorman138]

I'm not sure but our team is unable to change the Column type without affecting other un compiled code. That's why I'm bringing it all into mysql

[requinix]

That's fine because at no point did I say you had to change your column types

 

Try reading everything I said again. Maybe you'll catch the parts where I told you that strings need quotes this time.

[thenorman138]

I misunderstood the last part of what you said about being concerned that the line wasn't already a DATE. I thought you meant the true data type

[requinix]

Okay, let me backtrack on what I said. Yeah, if it's not a DATE or DATETIME then yeah, you should change it. You don't have to. But you should. Because it's the best tool for the job.

 

Also, if you're storing it in YYYYMMDD format then you don't have to reformat the value to compare for dates. You can compare them as strings too. Just make sure the value you're comparing with is also formatted as YYYYMMDD.

[ignace]

I'm not sure but our team is unable to change the Column type without affecting other un compiled code. That's why I'm bringing it all into mysql

If unable to change the data type, create a new column instead with a DATE type and query against that.

[Barand]

In a previous post of yours you use convert() with your DB2 dates

 

 

:SHIP is coming from DB2 and it is a packed field value (decimal). Expire_date is in mysql and it is DATE

 

What does this give as a result value?

$stmt = $MysqlConn->prepare("SELECT convert(:ship, DATE)");
$stmt->execute( [ ':ship' => $row2['ship'] ]);
$date = $stmt->fetchColumn();
echo $date;

 

@barand that gives me this format:  2018-01-19

 

Does that approach not work here?