Mysql throw error of incorrect DATE value that doesn't exist

user_14671559 0
Viewed 29

Error:

General error: 1525 Incorrect DATE value: ''

SQL:

select count(*) as aggregate from (
    (select `payments`.`id`, null AS user_id, `order_id`, `payment_via_id` as `via_id`, null AS type_id, null AS product_type_id, null AS employee_id, `sponsorship_client_id`, `value`, `date`, `orders`.`code` as `description`, `note`, null AS employee_name, null AS receipt_path, `payments`.`confirmed_at`, `payments`.`is_confirmed`, `payments`.`created_at`, `payments`.`updated_at` 
    from `payments` 
    inner join `orders` on `payments`.`order_id` = `orders`.`id` where `payments`.`date` is not null) 
    union all 
    (select `id`, `user_id`, null AS order_id, `expense_via_id` as `via_id`, `expense_type_id` as `type_id`, `product_type_id`, `employee_id`, null AS sponsorship_client_id, `value`, `date`, `description`, null AS note, `employee_name`, `receipt_path`, `confirmed_at`, `is_confirmed`, `created_at`, `updated_at` from `expenses`)
    ) AS merged where `user_id` = 3 or `user_id` is null and date(`created_at`) = 2022-02-01
)

Can anyone explain me this? As far as i understood, the error is saying I'm trying to get a DATE with value of '' but I don't see any DATE of '', does it make any sense?

mysql
1 Answers

The DATE() function that you are using date(`created_at`) is meant to extract the date part from a datetime expression and requires a valid date/datetime value.

It looks like you have an empty value somewhere in your created_at column which will of course not be a valid DATETIME value.

user_45179640
Related