SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: '23-05-2023'

Need Help
1

Hello,

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? :slight_smile:

2
  1. Why are you doing new DateTime() when all you actually want is a Date?
  2. Why are you not using FrozenDate, e.g. $today = FrozenDate::now()?
  3. 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.
  4. Why are you formatting the date as d-m-Y, when SQL queries want them formatted as Y-m-d?
  5. Why are you passing 'all' when that’s the default?
  6. Why are you using the old array notation?

So, try simply

$seriesinvoices = $this->SeriesInvoices->find()->where(['next_billing <=' => FrozenDate::now()]);
3

As Zuluru mentioned, you can use FrozenDate::now(). You can read more about it here Date & Time - 4.x

4

Thank you @Kaiser85 @Zuluru . It works :slight_smile: