Jump to content

db2 query and date comparison in PHP


thenorman138

Recommended Posts

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);
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.