I have a query that counts all recurring invoices that are due so far. Unfortunately I don’t understand the error exactly. Can you help me there?
// Load SerieBilling.
$this->SeriesInvoices = $this->fetchTable( 'SeriesInvoices' );
$today = new DateTime();
$today = $today->format( 'd-m-Y' ); //23-05-2023
$seriesinvoices = $this->SeriesInvoices->find( 'all' )->where( array('next_billing <=' => $today) ));
I get the following MySQL error:
Possibly related to PDOException: "SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: '23-05-2023'"
The columns type for the field next_billing are date. Not datetime.
Can you help me?
- Why are you doing
new DateTime() when all you actually want is a
- Why are you not using
$today = FrozenDate::now()?
- Why are you formatting the date at all? The query builder can take a date object and format it as it needs for the query.
- Why are you formatting the date as d-m-Y, when SQL queries want them formatted as Y-m-d?
- Why are you passing
'all' when that’s the default?
- Why are you using the old
So, try simply
$seriesinvoices = $this->SeriesInvoices->find()->where(['next_billing <=' => FrozenDate::now()]);
As Zuluru mentioned, you can use
FrozenDate::now(). You can read more about it here Date & Time - 4.x
Thank you @Kaiser85 @Zuluru . It works