ORA-01861 Literal Does Not Match Format String - Fix Date Format Mismatches

ORA-01861: Literal Does Not Match Format String

Error Overview

Error Text: ORA-01861: literal does not match format string

This error occurs when there’s a mismatch between a date/time literal value and the format model specified in TO_DATE, TO_TIMESTAMP, or similar conversion functions. The literal string structure doesn’t align with the format mask pattern Oracle expects.

Understanding Format String Matching

Format Model Components

Format String Elements
├── Date Components (YYYY, MM, DD)
├── Time Components (HH24, MI, SS)
├── Separators (-, /, :, space)
├── Text Elements (MON, DAY, MONTH)
├── Special Characters (", ')
└── Format Modifiers (FM, FX)

Common Scenarios

• Separator mismatches - Using / when format expects -
• Component order - YMD vs DMY vs MDY ordering
• Missing components - Time missing when format expects it
• Extra characters - Spaces, quotes, or other unexpected characters
• Case sensitivity - With FX modifier or text elements

Diagnostic Steps

1. Identify Format Mismatches

-- Test various format combinations
DECLARE
  v_test_date VARCHAR2(30) := '2024/01/15 10:30:45';
  v_result DATE;
  TYPE format_list IS TABLE OF VARCHAR2(30);
  v_formats format_list := format_list(
    'YYYY-MM-DD HH24:MI:SS',  -- Wrong separator
    'YYYY/MM/DD HH24:MI:SS',  -- Correct separator
    'DD/MM/YYYY HH24:MI:SS',  -- Wrong order
    'YYYY/MM/DD',             -- Missing time
    'YYYY/MM/DD HH:MI:SS'     -- Wrong hour format
  );
BEGIN
  DBMS_OUTPUT.PUT_LINE('Testing: ' || v_test_date);

  FOR i IN 1..v_formats.COUNT LOOP
    BEGIN
      v_result := TO_DATE(v_test_date, v_formats(i));
      DBMS_OUTPUT.PUT_LINE('✓ Format "' || v_formats(i) || '" - SUCCESS');
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -1861 THEN
          DBMS_OUTPUT.PUT_LINE('✗ Format "' || v_formats(i) || '" - Literal mismatch');
        ELSE
          DBMS_OUTPUT.PUT_LINE('✗ Format "' || v_formats(i) || '" - ' || SQLERRM);
        END IF;
    END;
  END LOOP;
END;
/

-- Analyze literal vs format character by character
CREATE OR REPLACE FUNCTION analyze_format_mismatch(
  p_literal VARCHAR2,
  p_format VARCHAR2
) RETURN VARCHAR2 AS
  v_analysis VARCHAR2(4000);
  v_lit_len NUMBER;
  v_fmt_len NUMBER;
BEGIN
  v_lit_len := LENGTH(p_literal);
  v_fmt_len := LENGTH(p_format);

  v_analysis := 'Literal: "' || p_literal || '" (length: ' || v_lit_len || ')' || CHR(10);
  v_analysis := v_analysis || 'Format:  "' || p_format || '" (length: ' || v_fmt_len || ')' || CHR(10);

  -- Check separators
  IF INSTR(p_literal, '/') > 0 AND INSTR(p_format, '/') = 0 THEN
    v_analysis := v_analysis || 'Issue: Literal uses "/" but format does not' || CHR(10);
  END IF;

  IF INSTR(p_literal, '-') > 0 AND INSTR(p_format, '-') = 0 THEN
    v_analysis := v_analysis || 'Issue: Literal uses "-" but format does not' || CHR(10);
  END IF;

  -- Check for time components
  IF REGEXP_LIKE(p_literal, '\d{2}:\d{2}:\d{2}') AND
     NOT REGEXP_LIKE(p_format, 'HH|MI|SS') THEN
    v_analysis := v_analysis || 'Issue: Literal contains time but format does not' || CHR(10);
  END IF;

  RETURN v_analysis;
END;
/

-- Test the analysis
SELECT
  literal,
  format,
  analyze_format_mismatch(literal, format) as analysis
FROM (
  SELECT '2024/01/15' as literal, 'YYYY-MM-DD' as format FROM dual UNION ALL
  SELECT '15-JAN-2024', 'DD/MON/YYYY' FROM dual UNION ALL
  SELECT '2024-01-15 10:30:45', 'YYYY-MM-DD' FROM dual
);

2. Detect Literal Patterns

-- Create function to detect literal format
CREATE OR REPLACE FUNCTION detect_literal_format(
  p_literal VARCHAR2
) RETURN VARCHAR2 AS
  v_format VARCHAR2(50);
BEGIN
  -- Check common date patterns
  IF REGEXP_LIKE(p_literal, '^\d{4}-\d{2}-\d{2}$') THEN
    v_format := 'YYYY-MM-DD';
  ELSIF REGEXP_LIKE(p_literal, '^\d{4}/\d{2}/\d{2}$') THEN
    v_format := 'YYYY/MM/DD';
  ELSIF REGEXP_LIKE(p_literal, '^\d{2}/\d{2}/\d{4}$') THEN
    v_format := 'DD/MM/YYYY or MM/DD/YYYY';
  ELSIF REGEXP_LIKE(p_literal, '^\d{2}-[A-Z]{3}-\d{4}$', 'i') THEN
    v_format := 'DD-MON-YYYY';
  ELSIF REGEXP_LIKE(p_literal, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') THEN
    v_format := 'YYYY-MM-DD HH24:MI:SS';
  ELSIF REGEXP_LIKE(p_literal, '^\d{4}/\d{2}/\d{2} \d{2}:\d{2}:\d{2}$') THEN
    v_format := 'YYYY/MM/DD HH24:MI:SS';
  ELSIF REGEXP_LIKE(p_literal, '^\d{2}-[A-Z]{3}-\d{4} \d{2}:\d{2}:\d{2}$', 'i') THEN
    v_format := 'DD-MON-YYYY HH24:MI:SS';
  ELSE
    v_format := 'Unknown pattern';
  END IF;

  RETURN v_format;
END;
/

-- Find mismatched formats in data
SELECT
  date_literal,
  detect_literal_format(date_literal) as detected_format,
  expected_format,
  CASE
    WHEN detect_literal_format(date_literal) = expected_format THEN 'Match'
    ELSE 'Mismatch'
  END as format_check
FROM (
  SELECT
    date_column as date_literal,
    'YYYY-MM-DD' as expected_format
  FROM import_table
  WHERE date_column IS NOT NULL
);

3. Test Format Strictness

-- Test with FX (Format eXact) modifier
DECLARE
  v_date DATE;
BEGIN
  -- Without FX - more lenient
  BEGIN
    v_date := TO_DATE('2024-1-5', 'YYYY-MM-DD');
    DBMS_OUTPUT.PUT_LINE('Without FX: "2024-1-5" accepted');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Without FX: Failed - ' || SQLERRM);
  END;

  -- With FX - strict matching
  BEGIN
    v_date := TO_DATE('2024-1-5', 'FXYYYY-MM-DD');
    DBMS_OUTPUT.PUT_LINE('With FX: "2024-1-5" accepted');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('With FX: Failed - ' || SQLERRM);
  END;

  -- Proper format with FX
  BEGIN
    v_date := TO_DATE('2024-01-05', 'FXYYYY-MM-DD');
    DBMS_OUTPUT.PUT_LINE('With FX: "2024-01-05" accepted');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('With FX: Failed - ' || SQLERRM);
  END;
END;
/

Immediate Solutions

Solution 1: Align Format with Literal

Match Separators and Order

-- Fix separator mismatches
-- Wrong: Different separators
SELECT TO_DATE('2024/01/15', 'YYYY-MM-DD') FROM dual;  -- Error

-- Correct: Matching separators
SELECT TO_DATE('2024/01/15', 'YYYY/MM/DD') FROM dual;  -- Success
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM dual;  -- Success

-- Create flexible converter
CREATE OR REPLACE FUNCTION convert_with_auto_format(
  p_literal VARCHAR2
) RETURN DATE AS
  v_detected_format VARCHAR2(50);
  v_date DATE;
BEGIN
  -- Detect format
  v_detected_format := detect_literal_format(p_literal);

  -- Handle ambiguous cases
  IF v_detected_format = 'DD/MM/YYYY or MM/DD/YYYY' THEN
    -- Try both formats
    BEGIN
      v_date := TO_DATE(p_literal, 'DD/MM/YYYY');
      RETURN v_date;
    EXCEPTION
      WHEN OTHERS THEN
        v_date := TO_DATE(p_literal, 'MM/DD/YYYY');
        RETURN v_date;
    END;
  ELSIF v_detected_format != 'Unknown pattern' THEN
    -- Use detected format
    RETURN TO_DATE(p_literal, v_detected_format);
  ELSE
    RAISE_APPLICATION_ERROR(-20001, 'Cannot detect format for: ' || p_literal);
  END IF;
END;
/

-- Batch conversion with format detection
UPDATE import_table
SET converted_date = convert_with_auto_format(date_literal)
WHERE date_literal IS NOT NULL
  AND converted_date IS NULL;

Handle Component Mismatches

-- Fix missing time components
CREATE OR REPLACE FUNCTION safe_date_convert(
  p_literal VARCHAR2,
  p_format VARCHAR2,
  p_default_time VARCHAR2 DEFAULT '00:00:00'
) RETURN DATE AS
  v_adjusted_literal VARCHAR2(100);
  v_adjusted_format VARCHAR2(100);
BEGIN
  v_adjusted_literal := p_literal;
  v_adjusted_format := p_format;

  -- Check if format expects time but literal doesn't have it
  IF REGEXP_LIKE(p_format, 'HH|MI|SS') AND
     NOT REGEXP_LIKE(p_literal, '\d{2}:\d{2}') THEN
    -- Append default time
    v_adjusted_literal := p_literal || ' ' || p_default_time;
  END IF;

  -- Check if literal has time but format doesn't
  IF REGEXP_LIKE(p_literal, '\d{2}:\d{2}') AND
     NOT REGEXP_LIKE(p_format, 'HH|MI|SS') THEN
    -- Add time to format
    v_adjusted_format := p_format || ' HH24:MI:SS';
  END IF;

  RETURN TO_DATE(v_adjusted_literal, v_adjusted_format);
END;
/

-- Usage examples
SELECT
  safe_date_convert('2024-01-15', 'YYYY-MM-DD HH24:MI:SS') as with_default_time,
  safe_date_convert('2024-01-15 10:30:45', 'YYYY-MM-DD') as auto_time_format
FROM dual;

Solution 2: Normalize Literals

Standardize Input Format

-- Create normalization function
CREATE OR REPLACE FUNCTION normalize_date_literal(
  p_literal VARCHAR2,
  p_target_format VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS'
) RETURN VARCHAR2 AS
  v_normalized VARCHAR2(100);
BEGIN
  v_normalized := TRIM(p_literal);

  -- Standardize separators based on target format
  IF INSTR(p_target_format, '-') > 0 THEN
    -- Replace all separators with dash
    v_normalized := REGEXP_REPLACE(v_normalized, '[/.]', '-');
  ELSIF INSTR(p_target_format, '/') > 0 THEN
    -- Replace all separators with slash
    v_normalized := REGEXP_REPLACE(v_normalized, '[-.]', '/');
  END IF;

  -- Pad single digits with zeros
  v_normalized := REGEXP_REPLACE(v_normalized, '(\D)(\d)(\D)', '\g1'||'0\g2'||'\g3');
  v_normalized := REGEXP_REPLACE(v_normalized, '^(\d)(\D)', '0\g1'||'\g2');
  v_normalized := REGEXP_REPLACE(v_normalized, '(\D)(\d)$', '\g1'||'0\g2');

  -- Add time if missing and expected
  IF REGEXP_LIKE(p_target_format, 'HH|MI|SS') AND
     NOT REGEXP_LIKE(v_normalized, '\d{2}:\d{2}') THEN
    v_normalized := v_normalized || ' 00:00:00';
  END IF;

  RETURN v_normalized;
END;
/

-- Test normalization
SELECT
  original,
  normalize_date_literal(original) as normalized,
  TO_DATE(normalize_date_literal(original), 'YYYY-MM-DD HH24:MI:SS') as converted
FROM (
  SELECT '2024/1/5' as original FROM dual UNION ALL
  SELECT '2024-01-15' FROM dual UNION ALL
  SELECT '2024.12.31 10:30:45' FROM dual
);

Solution 3: Dynamic Format Matching

Build Format from Literal

-- Create function to build matching format
CREATE OR REPLACE FUNCTION build_format_from_literal(
  p_literal VARCHAR2
) RETURN VARCHAR2 AS
  v_format VARCHAR2(100);
  v_char VARCHAR2(1);
  v_prev_type VARCHAR2(10) := 'START';
  v_curr_type VARCHAR2(10);
BEGIN
  FOR i IN 1..LENGTH(p_literal) LOOP
    v_char := SUBSTR(p_literal, i, 1);

    -- Determine character type
    IF v_char BETWEEN '0' AND '9' THEN
      v_curr_type := 'DIGIT';
    ELSIF v_char IN ('-', '/', '.', ':') THEN
      v_curr_type := 'SEP';
      v_format := v_format || v_char;
      v_prev_type := v_curr_type;
      CONTINUE;
    ELSIF v_char = ' ' THEN
      v_curr_type := 'SPACE';
      v_format := v_format || ' ';
      v_prev_type := v_curr_type;
      CONTINUE;
    ELSE
      v_curr_type := 'ALPHA';
    END IF;

    -- Build format based on position and context
    IF v_curr_type = 'DIGIT' AND v_prev_type != 'DIGIT' THEN
      -- Start of digit group
      IF i <= 4 THEN
        v_format := v_format || 'Y';  -- Likely year
      ELSIF REGEXP_LIKE(SUBSTR(p_literal, i-1, 3), ':\d\d') THEN
        v_format := v_format || 'M';  -- Likely minute
      ELSIF REGEXP_LIKE(SUBSTR(p_literal, i-1, 3), ' \d\d') THEN
        v_format := v_format || 'H';  -- Likely hour
      ELSE
        v_format := v_format || 'D';  -- Default to day
      END IF;
    ELSIF v_curr_type = 'DIGIT' AND v_prev_type = 'DIGIT' THEN
      -- Continue digit group
      v_format := v_format || SUBSTR(v_format, LENGTH(v_format), 1);
    ELSIF v_curr_type = 'ALPHA' THEN
      v_format := v_format || 'X';  -- Placeholder for text
    END IF;

    v_prev_type := v_curr_type;
  END LOOP;

  -- Clean up format
  v_format := REPLACE(v_format, 'YYYY', 'YYYY');
  v_format := REPLACE(v_format, 'DD', 'DD');
  v_format := REPLACE(v_format, 'MM', 'MM');
  v_format := REPLACE(v_format, 'HH', 'HH24');
  v_format := REPLACE(v_format, 'MM:MM', 'MI:SS');

  RETURN v_format;
END;
/

Long-Term Solutions

1. Implement Format Management System

Create Comprehensive Format Handler

CREATE OR REPLACE PACKAGE format_manager AS

  -- Format registration
  PROCEDURE register_format(
    p_name VARCHAR2,
    p_pattern VARCHAR2,
    p_oracle_format VARCHAR2,
    p_example VARCHAR2
  );

  -- Format matching
  FUNCTION find_matching_format(p_literal VARCHAR2) RETURN VARCHAR2;

  -- Conversion
  FUNCTION smart_convert(
    p_literal VARCHAR2,
    p_hint VARCHAR2 DEFAULT NULL
  ) RETURN DATE;

  -- Validation
  FUNCTION validate_literal_format(
    p_literal VARCHAR2,
    p_format VARCHAR2
  ) RETURN BOOLEAN;

END format_manager;
/

CREATE OR REPLACE PACKAGE BODY format_manager AS

  TYPE format_rec IS RECORD (
    name VARCHAR2(50),
    pattern VARCHAR2(200),
    oracle_format VARCHAR2(100),
    example VARCHAR2(50)
  );

  TYPE format_tab IS TABLE OF format_rec INDEX BY PLS_INTEGER;
  g_formats format_tab;

  PROCEDURE init_formats AS
  BEGIN
    -- Register common formats
    register_format('ISO Date', '^\d{4}-\d{2}-\d{2}$',
                   'YYYY-MM-DD', '2024-01-15');
    register_format('ISO DateTime', '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$',
                   'YYYY-MM-DD HH24:MI:SS', '2024-01-15 10:30:45');
    register_format('US Date', '^\d{2}/\d{2}/\d{4}$',
                   'MM/DD/YYYY', '01/15/2024');
    register_format('EU Date', '^\d{2}/\d{2}/\d{4}$',
                   'DD/MM/YYYY', '15/01/2024');
    register_format('Oracle Default', '^\d{2}-[A-Z]{3}-\d{2}$',
                   'DD-MON-RR', '15-JAN-24');
  END init_formats;

  PROCEDURE register_format(
    p_name VARCHAR2,
    p_pattern VARCHAR2,
    p_oracle_format VARCHAR2,
    p_example VARCHAR2
  ) AS
    v_idx NUMBER;
  BEGIN
    v_idx := g_formats.COUNT + 1;
    g_formats(v_idx).name := p_name;
    g_formats(v_idx).pattern := p_pattern;
    g_formats(v_idx).oracle_format := p_oracle_format;
    g_formats(v_idx).example := p_example;
  END register_format;

  FUNCTION find_matching_format(p_literal VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    IF g_formats.COUNT = 0 THEN
      init_formats;
    END IF;

    FOR i IN 1..g_formats.COUNT LOOP
      IF REGEXP_LIKE(p_literal, g_formats(i).pattern, 'i') THEN
        RETURN g_formats(i).oracle_format;
      END IF;
    END LOOP;

    RETURN NULL;
  END find_matching_format;

  FUNCTION smart_convert(
    p_literal VARCHAR2,
    p_hint VARCHAR2 DEFAULT NULL
  ) RETURN DATE AS
    v_format VARCHAR2(100);
  BEGIN
    -- Use hint if provided
    IF p_hint IS NOT NULL THEN
      BEGIN
        RETURN TO_DATE(p_literal, p_hint);
      EXCEPTION
        WHEN OTHERS THEN
          NULL;  -- Try auto-detection
      END;
    END IF;

    -- Auto-detect format
    v_format := find_matching_format(p_literal);

    IF v_format IS NOT NULL THEN
      RETURN TO_DATE(p_literal, v_format);
    ELSE
      RAISE_APPLICATION_ERROR(-20001,
        'No matching format found for: ' || p_literal);
    END IF;
  END smart_convert;

  FUNCTION validate_literal_format(
    p_literal VARCHAR2,
    p_format VARCHAR2
  ) RETURN BOOLEAN AS
    v_date DATE;
  BEGIN
    v_date := TO_DATE(p_literal, p_format);
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END validate_literal_format;

BEGIN
  init_formats;
END format_manager;
/

2. Data Quality Framework

Implement Validation and Correction

-- Create format validation table
CREATE TABLE format_validations (
  validation_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  table_name VARCHAR2(30),
  column_name VARCHAR2(30),
  expected_format VARCHAR2(100),
  validation_date DATE DEFAULT SYSDATE,
  total_records NUMBER,
  valid_records NUMBER,
  invalid_records NUMBER,
  status VARCHAR2(20)
);

-- Validation procedure
CREATE OR REPLACE PROCEDURE validate_date_formats(
  p_table VARCHAR2,
  p_column VARCHAR2,
  p_format VARCHAR2
) AS
  v_total NUMBER;
  v_valid NUMBER := 0;
  v_invalid NUMBER := 0;
  v_sql VARCHAR2(4000);
BEGIN
  -- Count total records
  EXECUTE IMMEDIATE
    'SELECT COUNT(*) FROM ' || p_table || ' WHERE ' || p_column || ' IS NOT NULL'
  INTO v_total;

  -- Count valid formats
  v_sql := 'SELECT COUNT(*) FROM ' || p_table ||
           ' WHERE ' || p_column || ' IS NOT NULL' ||
           ' AND format_manager.validate_literal_format(' ||
           p_column || ', ''' || p_format || ''')';

  EXECUTE IMMEDIATE v_sql INTO v_valid;
  v_invalid := v_total - v_valid;

  -- Log results
  INSERT INTO format_validations (
    table_name, column_name, expected_format,
    total_records, valid_records, invalid_records,
    status
  ) VALUES (
    p_table, p_column, p_format,
    v_total, v_valid, v_invalid,
    CASE WHEN v_invalid = 0 THEN 'PASSED' ELSE 'FAILED' END
  );

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Validation complete: ' || v_valid || '/' ||
                      v_total || ' records valid');
END;
/

Prevention Strategies

1. Input Standardization

-- Create standardization trigger
CREATE OR REPLACE TRIGGER standardize_date_input
BEFORE INSERT OR UPDATE ON data_table
FOR EACH ROW
DECLARE
  v_standardized VARCHAR2(30);
BEGIN
  IF :NEW.date_string IS NOT NULL THEN
    -- Detect and convert to standard format
    :NEW.date_string := TO_CHAR(
      format_manager.smart_convert(:NEW.date_string),
      'YYYY-MM-DD HH24:MI:SS'
    );
  END IF;
END;
/

2. Format Documentation

-- Create format documentation table
CREATE TABLE format_documentation (
  format_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  format_name VARCHAR2(50),
  oracle_format VARCHAR2(100),
  regex_pattern VARCHAR2(200),
  example VARCHAR2(50),
  description VARCHAR2(500),
  usage_notes VARCHAR2(1000)
);

-- Document standard formats
INSERT INTO format_documentation (
  format_name, oracle_format, example, description
) VALUES (
  'ISO 8601 Date', 'YYYY-MM-DD', '2024-01-15',
  'International standard date format'
);

Related Errors

• ORA-01830 - Date format picture ends before converting
• ORA-01843 - Not a valid month
• ORA-01847 - Day of month must be between 1 and last day
• ORA-01858 - Non-numeric character where numeric expected

Quick Reference

Emergency Response Steps

1. ✓ Check literal string structure
2. ✓ Verify format mask pattern
3. ✓ Match separators exactly
4. ✓ Ensure all components align
5. ✓ Check for extra/missing characters
6. ✓ Apply correct format mask

Quick Commands

-- Check literal format
SELECT detect_literal_format('2024/01/15') FROM dual;

-- Test format matching
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM dual;

-- Fix separator mismatch
SELECT TO_DATE(REPLACE('2024/01/15', '/', '-'), 'YYYY-MM-DD') FROM dual;

-- Handle missing time
SELECT TO_DATE('2024-01-15' || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- Use FX for exact matching
SELECT TO_DATE('2024-01-15', 'FXYYYY-MM-DD') FROM dual;

Prevention Guidelines

• Match formats exactly - Separators, order, and components
• Document formats - Clear specification of expected formats
• Validate on input - Check format before storage
• Standardize early - Convert to standard format on entry
• Handle variations - Account for different date formats
• Test thoroughly - Include all format variations in testing