Format specifier doesn't match the input string format (e.g., format '%Y%m%d' with input '2023-06-21') STR_TO_DATE() used inside a stored function or procedure with invalid input (returns error instead of NULL) Empty, NULL, or whitespace string passed to date functions when SQL mode is strict

Is the "1411" error serious?

Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

How to fix ERROR 1411: Incorrect value for function in MySQL

MySQLINTERMEDIATEHIGHUpdated Sep 12, 2026· 125 views

ERROR 1411 occurs when you pass an invalid or incorrectly formatted value to a MySQL function, most commonly with date/time functions like STR_TO_DATE(). The error happens when the input format doesn't match the format specifier or when using these functions in stored procedures.

Quick fix

Fixes 1411

-- WRONG: Input has dashes, format has no separators
SELECT STR_TO_DATE('2023-06-21', '%Y%m%d');
-- ERROR 1411

-- CORRECT: Match the separators
SELECT STR_TO_DATE('2023-06-21', '%Y-%m-%d');
-- Returns: 2023-06-21

-- Examples of common format patterns:
STR_TO_DATE('2023-06-21', '%Y-%m-%d')    -- dashes
STR_TO_DATE('06/21/2023', '%m/%d/%Y')    -- slashes
STR_TO_DATE('2023-06-21 14:30:00', '%Y-%m-%d %H:%i:%S')  -- datetime

Match your format string to your input data exactly
Add error handling for stored procedures and functions
Validate date strings before parsing
Check and adjust your SQL mode settings

mysql — error

$-- WRONG: Input has dashes, format has no separators
SELECT STR_TO_DATE('2023-06-21', '%Y%m%d');
-- ERROR 1411
-- CORRECT: Match the separators
SELECT STR_TO_DATE('2023-06-21', '%Y-%m-%d');
-- Returns: 2023-06-21
-- Examples of common format patterns:
STR_TO_DATE('2023-06-21', '%Y-%m-%d')    -- dashes
STR_TO_DATE('06/21/2023', '%m/%d/%Y')    -- slashes
STR_TO_DATE('2023-06-21 14:30:00', '%Y-%m-%d %H:%i:%S')  -- datetime
ERROR 1411: Incorrect value for function

What this error means

ERROR 1411 (ER_WRONG_VALUE_FOR_TYPE) is triggered when MySQL receives an invalid parameter for a function. The error message format is 'Incorrect %s value: \'%s\' for function %s'. This most frequently occurs with date/time functions like STR_TO_DATE(), DATE_FORMAT(), and TIMESTAMP(). When you call STR_TO_DATE('2023-06-21', '%Y%m%d'), MySQL expects the input string to have no separators (20230621) because the format pattern has no separators. If your input has dashes or slashes, the function fails. A critical behavior difference exists in MySQL: a direct SELECT statement with an invalid date may return NULL (no error), but the same code inside a stored function or procedure throws ERROR 1411. This inconsistency has been documented as a known issue in MySQL's bug tracker.

Typical symptoms

SQL query fails with 'ERROR 1411' when using date functions like STR_TO_DATE()
Error message shows format mismatch, e.g., 'Incorrect datetime value: \'26-09-2021\' for function str_to_date'
STR_TO_DATE() works in a direct SELECT statement but fails inside a stored procedure or function
Empty or whitespace date values cause error 1411 instead of returning NULL
Queries that worked in older MySQL versions now fail in stricter versions (8.0+)
CI/CD pipeline fails when importing data with date parsing functions

Common causes

Format specifier doesn't match the input string format (e.g., format '%Y%m%d' with input '2023-06-21')
STR_TO_DATE() used inside a stored function or procedure with invalid input (returns error instead of NULL)
Empty, NULL, or whitespace string passed to date functions when SQL mode is strict
MySQL version difference: newer versions (8.0+) are stricter about NO_ZERO_DATE and STRICT_TRANS_TABLES SQL modes
Incorrect handling of zero dates like '0000-00-00 00:00:00' when NO_ZERO_DATE SQL mode is enabled
Character encoding or special character issues in the date string preventing proper parsing

How to fix "ERROR 1411: Incorrect value for function"

1Match your format string to your input data exactly

The most common cause is a mismatch between your input format and the format pattern. Ensure every separator and digit count matches.

sql

-- WRONG: Input has dashes, format has no separators
SELECT STR_TO_DATE('2023-06-21', '%Y%m%d');
-- ERROR 1411

-- CORRECT: Match the separators
SELECT STR_TO_DATE('2023-06-21', '%Y-%m-%d');
-- Returns: 2023-06-21

-- Examples of common format patterns:
STR_TO_DATE('2023-06-21', '%Y-%m-%d')    -- dashes
STR_TO_DATE('06/21/2023', '%m/%d/%Y')    -- slashes
STR_TO_DATE('2023-06-21 14:30:00', '%Y-%m-%d %H:%i:%S')  -- datetime

Difference in character count matters: %Y is 4 digits, %m and %d are 2 digits each.

2Add error handling for stored procedures and functions

If using STR_TO_DATE() inside a stored procedure or function, add a CONTINUE HANDLER to prevent ERROR 1411 from stopping execution.

sql

DELIMITER //
CREATE FUNCTION parse_date(date_str VARCHAR(50))
RETURNS DATE
DETERMINISTIC
READS SQL DATA
BEGIN
  DECLARE result DATE;
  -- Handle error 1411 gracefully
  DECLARE CONTINUE HANDLER FOR 1411 SET result = NULL;
  
  SET result = STR_TO_DATE(date_str, '%Y-%m-%d');
  RETURN result;
END//
DELIMITER ;

-- Now the function returns NULL instead of throwing error
SELECT parse_date('invalid-date');
-- Returns: NULL (no error)

Alternatively, use SELECT INTO pattern which is less prone to this issue:

sql

DELIMITER //
CREATE FUNCTION parse_date(date_str VARCHAR(50))
RETURNS DATE
BEGIN
  DECLARE result DATE DEFAULT NULL;
  SELECT STR_TO_DATE(date_str, '%Y-%m-%d') INTO result;
  RETURN COALESCE(result, NULL);
END//
DELIMITER ;

3Validate date strings before parsing

Pre-validate your input to ensure it's not empty, NULL, or malformed before passing to date functions.

sql

-- Check for empty/NULL before parsing
SELECT 
  CASE 
    WHEN date_column IS NULL OR date_column = '' THEN NULL
    ELSE STR_TO_DATE(date_column, '%Y-%m-%d')
  END as parsed_date
FROM users;

-- Or in an INSERT statement:
INSERT INTO events (event_date)
SELECT 
  CASE 
    WHEN date_string = '' THEN NULL
    ELSE STR_TO_DATE(date_string, '%Y-%m-%d')
  END
FROM raw_data;

-- Pattern matching validation:
SELECT 
  CASE 
    WHEN date_column REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
    THEN STR_TO_DATE(date_column, '%Y-%m-%d')
    ELSE NULL
  END as parsed_date
FROM users;

This prevents passing invalid strings to the function.

4Check and adjust your SQL mode settings

MySQL's strict SQL modes (introduced in 5.7, default in 8.0) enforce stricter validation. Check your current SQL mode and adjust if needed.

sql

-- Check current SQL mode
SELECT @@sql_mode;

-- If you see STRICT_TRANS_TABLES or NO_ZERO_DATE:
-- Option 1: Disable strict mode (NOT recommended for production)
SET GLOBAL sql_mode = '';
SET SESSION sql_mode = '';

-- Option 2: Keep strict mode but handle errors properly
-- (Recommended) - Use CASE statements and error handlers instead

-- Verify the new setting
SELECT @@sql_mode;

-- If changing global mode, restart MySQL for persistence:
-- Edit /etc/mysql/my.cnf and set sql_mode = ''

Important: Only disable strict mode if absolutely necessary for legacy code. It's better to fix the root cause (format mismatch) than to disable safety features.

5Use alternative date functions if available

If STR_TO_DATE() continues to cause issues, consider using alternative approaches depending on your data.

sql

-- If your date is already in ISO format (YYYY-MM-DD):
-- Use CAST instead of STR_TO_DATE
SELECT CAST('2023-06-21' AS DATE);

-- If you have Unix timestamp:
SELECT FROM_UNIXTIME(1687310400);

-- If data is in MySQL DATE already:
SELECT date_column FROM users;

-- For complex formats, use JSON functions or substring extraction:
SELECT 
  DATE(
    CONCAT(
      SUBSTRING(date_str, 1, 4), '-',
      SUBSTRING(date_str, 5, 2), '-',
      SUBSTRING(date_str, 7, 2)
    )
  ) as parsed_date
FROM raw_data;

These alternatives may work better depending on your data source.

Advanced notes

Known MySQL Bug (Bug #111526): When STR_TO_DATE() is used inside a stored function with an invalid date value, MySQL throws ERROR 1411 instead of returning NULL as expected. A direct SELECT with the same invalid value returns NULL without error. This inconsistency between stored functions and direct queries is documented as a verified bug. Workaround: Use DECLARE CONTINUE HANDLER FOR 1411 or avoid STR_TO_DATE() in stored functions when possible.

SQL Mode Behavior: MySQL 5.7 introduced STRICT_TRANS_TABLES as default, and MySQL 8.0 made it mandatory. This mode enforces stricter date validation. If migrating from MySQL 5.6 to 8.0, you may encounter this error for code that previously worked. The solution is to fix the format mismatch rather than downgrade SQL modes.

Performance Note: When bulk-importing data with date parsing, batch operations using SELECT INTO are typically faster and more reliable than row-by-row STR_TO_DATE() calls in stored procedures. Consider using LOAD DATA INFILE with proper field separators if you're importing large datasets.

Frequently asked questions

What does "1411" mean?: ERROR 1411 (ER_WRONG_VALUE_FOR_TYPE) is triggered when MySQL receives an invalid parameter for a function. The error message format is 'Incorrect %s value: \'%s\' for function %s'. This most frequently occurs with date/time functions like STR_TO_DATE(), DATE_FORMAT(), and TIMESTAMP().
How do I fix "1411"?: ERROR 1411 occurs when you pass an invalid or incorrectly formatted value to a MySQL function, most commonly with date/time functions like STR_TO_DATE(). The error happens when the input format doesn't match the format specifier or when using these functions in stored procedures.
What causes "1411"?: Format specifier doesn't match the input string format (e.g., format '%Y%m%d' with input '2023-06-21') STR_TO_DATE() used inside a stored function or procedure with invalid input (returns error instead of NULL) Empty, NULL, or whitespace string passed to date functions when SQL mode is strict
Is the "1411" error serious?: Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.