Mysql – Warning: #1292 Incorrect datetime error when using DATE_ADD()

datemariadbMySQL

Doing this works fine:

SELECT * FROM table WHERE foo < '2021-01-09 00:00:00'

But this one:

SELECT * FROM table WHERE DATE_ADD('foo', INTERVAL 5 DAY) < '2021-01-09 00:00:00'

results in

Warning: #1292 Incorrect datetime value: 'foo'

What could possibly be causing this?

(MariaDB 10.4.17)

Best Answer

You try to add to the string 'foo' 5 days.

Use backticks for column names

SELECT * FROM table WHERE DATE_ADD(`foo`, INTERVAL 5 DAY) < '2021-01-09 00:00:00'

Related Solutions

Mysql – Importing True/False as tinyint failing when last field

Your instinct is to use STR_TO_DATE() like you did to convert the string format but will not work "correcty" because of the warning.

Query #1

SELECT STR_TO_DATE('2019-02-27T13:15:08.000+0000','%Y-%m-%dT%H:%i:%s');

| STR_TO_DATE('2019-02-27T13:15:08.000+0000','%Y-%m-%dT%H:%i:%s') |
| --------------------------------------------------------------- |
| 2019-02-27 13:15:08                                             |

Query #2

SHOW WARNINGS;

| Level   | Code | Message                                                            |
| ------- | ---- | ------------------------------------------------------------------ |
| Warning | 1292 | Truncated incorrect datetime value: '2019-02-27T13:15:08.000+0000' |

So you will need to be a little more creative with SUBSTRING(), CONCAT() and a CAST()

Query

SELECT
  CAST (
    CONCAT(
       SUBSTRING('2019-02-27T13:15:08.000+0000', 1, 10)
     , ' '
     , SUBSTRING('2019-02-27T13:15:08.000+0000', 12, 8)
   )
   AS DATETIME  
  ) AS result;

Result

| result              |
| ------------------- |
| 2019-02-27 13:15:08 |

see demo

Mysql – Looking for some advice to complete custom thesql query against 3 different tables

Check

SELECT 
  t2.id, 
  MAX(CASE WHEN t1.`meta_key` = 'woocommerce_order_number' THEN t1.meta_value END) wcID,
  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division
FROM  wp_gf_entry_meta t1 
LEFT JOIN wp_gf_entry t2 
    ON t1.entry_id = t2.id
LEFT JOIN ( SELECT t3.ID 
            FROM wp_posts t3 
            WHERE t3.`post_status` IN ('wc-processing', 'wc-completed', 'wc-free-of-charge') 
              AND t3.`post_type` = 'shop_order' ) t4 
    ON t1.`meta_key` = 'woocommerce_order_number' AND t1.meta_value = t4.ID
WHERE t2.`form_id` = 71
  AND t2.`is_read` = 1 
  AND t2.`status` = 'active'
  AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')
GROUP BY t2.id

fiddle

Best Answer

Related Solutions

Mysql – Importing True/False as tinyint failing when last field

Mysql – Looking for some advice to complete custom thesql query against 3 different tables

Related Question