📋

Description

This error indicates that MySQL attempted to convert a value to a different data type during an operation (like INSERT or UPDATE), but the conversion failed or resulted in an invalid or truncated value. It typically occurs when the provided data does not match the expected data type or format of the target column, or exceeds its defined capacity.

💬

Error Message

Truncated incorrect %s value: '%s'

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

Attempting to insert a value into a column whose data type is incompatible, such as inserting a non-numeric string into an INT column.

⚠️

Value Exceeds Column Capacity

Providing a value that is too long for a string column (e.g., VARCHAR(10)) or too large/small for a numeric column (e.g., TINYINT).

⚠️

Incorrect Date/Time Format

Supplying a date or time string that does not conform to MySQL's expected format for DATE, DATETIME, or TIMESTAMP columns.

⚠️

Strict SQL Mode

When MySQL is running in a strict SQL mode (e.g., TRADITIONAL), it will reject values that would otherwise be truncated or cause data loss in non-strict modes.

🛠️

Solutions

5 solutions available

1. Use Correct Date Format easy

MySQL expects YYYY-MM-DD format

1

Use standard date format

-- Wrong formats:
-- '01/15/2024' (MM/DD/YYYY)
-- '15-01-2024' (DD-MM-YYYY)
-- 'January 15, 2024'

-- Right format:
INSERT INTO events (event_date) VALUES ('2024-01-15');

2

For datetime

INSERT INTO events (event_time) VALUES ('2024-01-15 14:30:00');

2. Use STR_TO_DATE for Non-Standard Formats easy

Convert string to date with format specifier

1

Convert common formats

-- MM/DD/YYYY
INSERT INTO events (event_date) 
VALUES (STR_TO_DATE('01/15/2024', '%m/%d/%Y'));

-- DD-MM-YYYY
INSERT INTO events (event_date) 
VALUES (STR_TO_DATE('15-01-2024', '%d-%m-%Y'));

-- Month name
INSERT INTO events (event_date) 
VALUES (STR_TO_DATE('January 15, 2024', '%M %d, %Y'));

2

Common format specifiers

-- %Y = 4-digit year (2024)
-- %y = 2-digit year (24)
-- %m = month number (01-12)
-- %d = day of month (01-31)
-- %H = hour 24h (00-23)
-- %i = minutes (00-59)
-- %s = seconds (00-59)
-- %M = month name (January)

3. Fix Invalid Date Values easy

Ensure date values are valid

1

Check for invalid dates

-- Invalid dates (strict mode rejects):
-- '2024-02-30' (February doesn't have 30 days)
-- '2024-13-01' (no month 13)
-- '2024-00-15' (no month 0)
-- '0000-00-00' (zero date)

-- Validate before inserting:
SELECT STR_TO_DATE('2024-02-30', '%Y-%m-%d');  -- Returns NULL if invalid

4. Handle Timezone Issues medium

Convert timezone-aware strings properly

1

Use CONVERT_TZ for timezone strings

-- If you have UTC timestamp:
INSERT INTO events (event_time)
VALUES (CONVERT_TZ('2024-01-15 14:30:00', '+00:00', @@session.time_zone));

2

Check session timezone

SELECT @@session.time_zone;

5. Fix Application Date Formatting medium

Format dates correctly before sending to MySQL

1

JavaScript/Node.js

const date = new Date();
const mysqlDate = date.toISOString().slice(0, 19).replace('T', ' ');
// '2024-01-15 14:30:00'

2

Python

from datetime import datetime
date = datetime.now()
mysql_date = date.strftime('%Y-%m-%d %H:%M:%S')
# '2024-01-15 14:30:00'

3

PHP

$date = new DateTime();
$mysqlDate = $date->format('Y-m-d H:i:s');
// '2024-01-15 14:30:00'

🔗

Related Errors

5 related errors

MySQL Error 1292: Truncated Incorrect Value