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

-- 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

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)

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 ;

-- 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;

-- 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 = ''

-- 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;