ORA-01830 Date Format Picture Ends Before Converting - Fix Date Format Errors

ORA-01830: Date Format Picture Ends Before Converting Entire Input String

Error Overview

Error Text: ORA-01830: date format picture ends before converting entire input string

This error occurs when the date format mask provided to TO_DATE or similar functions is shorter than the actual input string, leaving unconverted characters. Oracle cannot parse the entire date string because the format model doesn’t account for all characters in the input.

Understanding Date Format Models

Oracle Date Format Elements

Common Format Elements
├── YYYY/YY - Year (4/2 digits)
├── MM - Month (01-12)
├── MON/MONTH - Month name
├── DD - Day of month (01-31)
├── HH24/HH12 - Hour (24/12 hour format)
├── MI - Minutes (00-59)
├── SS - Seconds (00-59)
├── FF - Fractional seconds
└── AM/PM - Meridian indicator

Common Scenarios

• Extra characters - Input has more characters than format expects
• Time components missing - Format lacks time when input includes it
• Timezone information - Input includes timezone not in format
• Milliseconds - Fractional seconds not accounted for
• Extra spaces or characters - Trailing spaces or special characters

Diagnostic Steps

1. Identify Format Mismatches

-- Test date conversion with various formats
DECLARE
  v_date_string VARCHAR2(100) := '2024-01-15 14:30:45.123';
  v_date DATE;
BEGIN
  -- Test different format masks
  BEGIN
    v_date := TO_DATE(v_date_string, 'YYYY-MM-DD');
    DBMS_OUTPUT.PUT_LINE('Format YYYY-MM-DD: Success');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Format YYYY-MM-DD: ' || SQLERRM);
  END;

  BEGIN
    v_date := TO_DATE(v_date_string, 'YYYY-MM-DD HH24:MI:SS');
    DBMS_OUTPUT.PUT_LINE('Format YYYY-MM-DD HH24:MI:SS: Success');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Format YYYY-MM-DD HH24:MI:SS: ' || SQLERRM);
  END;

  BEGIN
    -- This should work if we use TIMESTAMP
    v_date := TO_DATE(SUBSTR(v_date_string, 1, 19), 'YYYY-MM-DD HH24:MI:SS');
    DBMS_OUTPUT.PUT_LINE('Truncated string: Success');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Truncated string: ' || SQLERRM);
  END;
END;
/

-- Check actual string lengths vs format lengths
SELECT
  date_column,
  LENGTH(date_column) as string_length,
  'YYYY-MM-DD' as format_mask,
  LENGTH('YYYY-MM-DD') as format_length,
  CASE
    WHEN LENGTH(date_column) > 10 THEN 'String too long for format'
    WHEN LENGTH(date_column) < 10 THEN 'String too short for format'
    ELSE 'Length matches'
  END as length_check
FROM your_table
WHERE date_column IS NOT NULL;

-- Find problematic date strings
SELECT DISTINCT
  date_string,
  LENGTH(date_string) as len,
  REGEXP_COUNT(date_string, '\d') as digit_count,
  REGEXP_COUNT(date_string, '[:-]') as separator_count,
  CASE
    WHEN REGEXP_LIKE(date_string, '^\d{4}-\d{2}-\d{2}$') THEN 'YYYY-MM-DD'
    WHEN REGEXP_LIKE(date_string, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') THEN 'YYYY-MM-DD HH24:MI:SS'
    WHEN REGEXP_LIKE(date_string, '^\d{2}/\d{2}/\d{4}$') THEN 'DD/MM/YYYY'
    ELSE 'Non-standard format'
  END as detected_format
FROM import_table
WHERE date_string IS NOT NULL;

2. Analyze Date String Patterns

-- Create function to analyze date strings
CREATE OR REPLACE FUNCTION analyze_date_string(
  p_date_string VARCHAR2
) RETURN VARCHAR2 AS
  v_analysis VARCHAR2(4000);
BEGIN
  v_analysis := 'String: ' || p_date_string || CHR(10);
  v_analysis := v_analysis || 'Length: ' || LENGTH(p_date_string) || CHR(10);

  -- Check for common patterns
  IF REGEXP_LIKE(p_date_string, '^\d{4}-\d{2}-\d{2}$') THEN
    v_analysis := v_analysis || 'Pattern: YYYY-MM-DD (date only)' || CHR(10);
  ELSIF REGEXP_LIKE(p_date_string, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') THEN
    v_analysis := v_analysis || 'Pattern: YYYY-MM-DD HH24:MI:SS (datetime)' || CHR(10);
  ELSIF REGEXP_LIKE(p_date_string, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d+$') THEN
    v_analysis := v_analysis || 'Pattern: YYYY-MM-DD HH24:MI:SS.FF (with fractional seconds)' || CHR(10);
  ELSIF REGEXP_LIKE(p_date_string, '.*[A-Z]{3}$') THEN
    v_analysis := v_analysis || 'Pattern: Contains timezone (ends with 3 letters)' || CHR(10);
  ELSE
    v_analysis := v_analysis || 'Pattern: Non-standard or complex' || CHR(10);
  END IF;

  -- Check for special characters
  IF INSTR(p_date_string, 'T') > 0 THEN
    v_analysis := v_analysis || 'Contains T separator (ISO 8601)' || CHR(10);
  END IF;

  IF REGEXP_LIKE(p_date_string, '.*[+-]\d{2}:\d{2}$') THEN
    v_analysis := v_analysis || 'Contains timezone offset' || CHR(10);
  END IF;

  RETURN v_analysis;
END;
/

-- Test the analysis function
SELECT
  date_string,
  analyze_date_string(date_string) as analysis
FROM (
  SELECT '2024-01-15' as date_string FROM dual UNION ALL
  SELECT '2024-01-15 14:30:45' FROM dual UNION ALL
  SELECT '2024-01-15 14:30:45.123' FROM dual UNION ALL
  SELECT '2024-01-15T14:30:45Z' FROM dual UNION ALL
  SELECT '2024-01-15 14:30:45 PST' FROM dual
);

3. Test Format Compatibility

-- Create comprehensive format testing procedure
CREATE OR REPLACE PROCEDURE test_date_formats(
  p_date_string VARCHAR2
) AS
  TYPE format_array IS TABLE OF VARCHAR2(50);
  v_formats format_array := format_array(
    'YYYY-MM-DD',
    'YYYY-MM-DD HH24:MI:SS',
    'YYYY-MM-DD HH24:MI:SS.FF',
    'YYYY-MM-DD"T"HH24:MI:SS',
    'DD/MM/YYYY',
    'MM/DD/YYYY',
    'DD-MON-YYYY',
    'DD-MON-YYYY HH24:MI:SS',
    'YYYY-MM-DD HH24:MI:SS TZH:TZM',
    'YYYYMMDD',
    'YYYYMMDDHH24MISS'
  );
  v_result DATE;
  v_success BOOLEAN;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Testing string: ' || p_date_string);
  DBMS_OUTPUT.PUT_LINE('String length: ' || LENGTH(p_date_string));
  DBMS_OUTPUT.PUT_LINE('-----------------------------------');

  FOR i IN 1..v_formats.COUNT LOOP
    v_success := FALSE;
    BEGIN
      v_result := TO_DATE(p_date_string, v_formats(i));
      v_success := TRUE;
      DBMS_OUTPUT.PUT_LINE('✓ ' || v_formats(i) || ' - SUCCESS');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('✗ ' || v_formats(i) || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

-- Test with problematic string
EXEC test_date_formats('2024-01-15 14:30:45.123');

Immediate Solutions

Solution 1: Match Format to Input String

Adjust Format Mask to Match Input

-- Fix by using complete format mask
-- Problem: Format too short
SELECT TO_DATE('2024-01-15 14:30:45', 'YYYY-MM-DD')  -- Error: ORA-01830
FROM dual;

-- Solution: Use complete format
SELECT TO_DATE('2024-01-15 14:30:45', 'YYYY-MM-DD HH24:MI:SS')  -- Success
FROM dual;

-- Handle fractional seconds with TIMESTAMP
SELECT TO_TIMESTAMP('2024-01-15 14:30:45.123', 'YYYY-MM-DD HH24:MI:SS.FF3')
FROM dual;

-- Handle timezone information
SELECT TO_TIMESTAMP_TZ('2024-01-15 14:30:45 -08:00',
                       'YYYY-MM-DD HH24:MI:SS TZH:TZM')
FROM dual;

-- Handle ISO 8601 format with T separator
SELECT TO_DATE('2024-01-15T14:30:45', 'YYYY-MM-DD"T"HH24:MI:SS')
FROM dual;

Create Flexible Conversion Function

CREATE OR REPLACE FUNCTION flexible_to_date(
  p_date_string VARCHAR2,
  p_default_format VARCHAR2 DEFAULT NULL
) RETURN DATE AS
  v_date DATE;
  v_formats SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
    'YYYY-MM-DD HH24:MI:SS.FF',
    'YYYY-MM-DD HH24:MI:SS',
    'YYYY-MM-DD',
    'DD/MM/YYYY HH24:MI:SS',
    'DD/MM/YYYY',
    'MM/DD/YYYY HH24:MI:SS',
    'MM/DD/YYYY',
    'DD-MON-YYYY HH24:MI:SS',
    'DD-MON-YYYY',
    'YYYYMMDD HH24MISS',
    'YYYYMMDD'
  );
BEGIN
  -- Try default format first if provided
  IF p_default_format IS NOT NULL THEN
    BEGIN
      RETURN TO_DATE(p_date_string, p_default_format);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;  -- Try other formats
    END;
  END IF;

  -- Try each format until one works
  FOR i IN 1..v_formats.COUNT LOOP
    BEGIN
      -- Handle potential fractional seconds by using SUBSTR
      IF INSTR(v_formats(i), 'FF') > 0 THEN
        RETURN TO_DATE(SUBSTR(p_date_string, 1, 19),
                      SUBSTR(v_formats(i), 1, INSTR(v_formats(i), '.') - 1));
      ELSE
        RETURN TO_DATE(p_date_string, v_formats(i));
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;  -- Try next format
    END;
  END LOOP;

  -- If nothing worked, raise error
  RAISE_APPLICATION_ERROR(-20001,
    'Cannot convert "' || p_date_string || '" to date with any known format');
END;
/

-- Usage
SELECT
  flexible_to_date('2024-01-15') as date1,
  flexible_to_date('2024-01-15 14:30:45') as date2,
  flexible_to_date('15/01/2024') as date3,
  flexible_to_date('15-JAN-2024') as date4
FROM dual;

Solution 2: Truncate or Clean Input Strings

Remove Extra Characters

-- Truncate to expected length
SELECT
  TO_DATE(SUBSTR('2024-01-15 14:30:45.123', 1, 10), 'YYYY-MM-DD') as date_only,
  TO_DATE(SUBSTR('2024-01-15 14:30:45.123', 1, 19), 'YYYY-MM-DD HH24:MI:SS') as datetime
FROM dual;

-- Remove trailing spaces and special characters
SELECT TO_DATE(TRIM('2024-01-15  '), 'YYYY-MM-DD')
FROM dual;

-- Clean function for date strings
CREATE OR REPLACE FUNCTION clean_date_string(
  p_date_string VARCHAR2,
  p_keep_time BOOLEAN DEFAULT TRUE
) RETURN VARCHAR2 AS
  v_cleaned VARCHAR2(100);
BEGIN
  -- Remove leading/trailing spaces
  v_cleaned := TRIM(p_date_string);

  -- Remove timezone information if present
  v_cleaned := REGEXP_REPLACE(v_cleaned, ' [A-Z]{3,4}$', '');
  v_cleaned := REGEXP_REPLACE(v_cleaned, '[+-]\d{2}:\d{2}$', '');

  -- Remove fractional seconds if not keeping time details
  IF NOT p_keep_time THEN
    v_cleaned := REGEXP_SUBSTR(v_cleaned, '^\d{4}-\d{2}-\d{2}');
  ELSIF INSTR(v_cleaned, '.') > 0 THEN
    v_cleaned := SUBSTR(v_cleaned, 1, INSTR(v_cleaned, '.') - 1);
  END IF;

  -- Handle T separator in ISO 8601
  v_cleaned := REPLACE(v_cleaned, 'T', ' ');

  RETURN v_cleaned;
END;
/

-- Usage
SELECT
  original_string,
  clean_date_string(original_string) as cleaned,
  TO_DATE(clean_date_string(original_string), 'YYYY-MM-DD HH24:MI:SS') as converted_date
FROM (
  SELECT '2024-01-15 14:30:45.123 PST' as original_string FROM dual UNION ALL
  SELECT '2024-01-15T14:30:45Z' FROM dual UNION ALL
  SELECT '2024-01-15 14:30:45+08:00' FROM dual
);

Solution 3: Use Alternative Conversion Methods

Use CAST and Other Functions

-- Use CAST for standard formats
SELECT CAST('2024-01-15' AS DATE) FROM dual;

-- Use ANSI date literal for specific format
SELECT DATE '2024-01-15' FROM dual;

-- Use timestamp for complex strings
SELECT CAST(TO_TIMESTAMP('2024-01-15 14:30:45.123',
            'YYYY-MM-DD HH24:MI:SS.FF3') AS DATE)
FROM dual;

-- Extract date from complex string
CREATE OR REPLACE FUNCTION extract_date_parts(
  p_date_string VARCHAR2
) RETURN DATE AS
  v_year NUMBER;
  v_month NUMBER;
  v_day NUMBER;
  v_hour NUMBER := 0;
  v_minute NUMBER := 0;
  v_second NUMBER := 0;
BEGIN
  -- Extract date components using REGEXP
  v_year := TO_NUMBER(REGEXP_SUBSTR(p_date_string, '^\d{4}'));
  v_month := TO_NUMBER(REGEXP_SUBSTR(p_date_string, '\d{2}', 6));
  v_day := TO_NUMBER(REGEXP_SUBSTR(p_date_string, '\d{2}', 9));

  -- Extract time components if present
  IF LENGTH(p_date_string) > 10 THEN
    v_hour := NVL(TO_NUMBER(REGEXP_SUBSTR(p_date_string, '\d{2}', 12)), 0);
    v_minute := NVL(TO_NUMBER(REGEXP_SUBSTR(p_date_string, '\d{2}', 15)), 0);
    v_second := NVL(TO_NUMBER(REGEXP_SUBSTR(p_date_string, '\d{2}', 18)), 0);
  END IF;

  -- Construct date
  RETURN TO_DATE(v_year || '-' || v_month || '-' || v_day || ' ' ||
                 v_hour || ':' || v_minute || ':' || v_second,
                 'YYYY-MM-DD HH24:MI:SS');
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Cannot extract date from: ' || p_date_string);
END;
/

Long-Term Solutions

1. Implement Standardized Date Handling

Create Date Management Package

CREATE OR REPLACE PACKAGE date_converter AS

  -- Standard formats
  C_FORMAT_DATE CONSTANT VARCHAR2(30) := 'YYYY-MM-DD';
  C_FORMAT_DATETIME CONSTANT VARCHAR2(30) := 'YYYY-MM-DD HH24:MI:SS';
  C_FORMAT_TIMESTAMP CONSTANT VARCHAR2(30) := 'YYYY-MM-DD HH24:MI:SS.FF6';
  C_FORMAT_ISO8601 CONSTANT VARCHAR2(30) := 'YYYY-MM-DD"T"HH24:MI:SS';

  -- Conversion functions
  FUNCTION smart_to_date(
    p_string VARCHAR2,
    p_format VARCHAR2 DEFAULT NULL
  ) RETURN DATE;

  FUNCTION detect_format(p_string VARCHAR2) RETURN VARCHAR2;

  FUNCTION standardize_date_string(p_string VARCHAR2) RETURN VARCHAR2;

  PROCEDURE validate_date_string(
    p_string VARCHAR2,
    p_format OUT VARCHAR2,
    p_is_valid OUT BOOLEAN
  );

END date_converter;
/

CREATE OR REPLACE PACKAGE BODY date_converter AS

  FUNCTION detect_format(p_string VARCHAR2) RETURN VARCHAR2 AS
    v_clean_string VARCHAR2(100);
  BEGIN
    v_clean_string := TRIM(p_string);

    -- Check various patterns
    IF REGEXP_LIKE(v_clean_string, '^\d{4}-\d{2}-\d{2}$') THEN
      RETURN C_FORMAT_DATE;
    ELSIF REGEXP_LIKE(v_clean_string, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') THEN
      RETURN C_FORMAT_DATETIME;
    ELSIF REGEXP_LIKE(v_clean_string, '^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}') THEN
      RETURN C_FORMAT_ISO8601;
    ELSIF REGEXP_LIKE(v_clean_string, '^\d{2}/\d{2}/\d{4}$') THEN
      RETURN 'DD/MM/YYYY';  -- or MM/DD/YYYY based on locale
    ELSIF REGEXP_LIKE(v_clean_string, '^\d{2}-[A-Z]{3}-\d{4}$', 'i') THEN
      RETURN 'DD-MON-YYYY';
    ELSE
      RETURN NULL;
    END IF;
  END detect_format;

  FUNCTION standardize_date_string(p_string VARCHAR2) RETURN VARCHAR2 AS
    v_result VARCHAR2(100);
  BEGIN
    v_result := TRIM(p_string);

    -- Remove common suffixes
    v_result := REGEXP_REPLACE(v_result, '\.\d{3,6}', '');  -- Remove milliseconds
    v_result := REGEXP_REPLACE(v_result, ' [A-Z]{3,4}$', '');  -- Remove timezone
    v_result := REGEXP_REPLACE(v_result, '[+-]\d{2}:\d{2}$', '');  -- Remove offset
    v_result := REGEXP_REPLACE(v_result, 'Z$', '');  -- Remove Z timezone

    -- Standardize separators
    v_result := REPLACE(v_result, 'T', ' ');  -- ISO 8601

    RETURN v_result;
  END standardize_date_string;

  FUNCTION smart_to_date(
    p_string VARCHAR2,
    p_format VARCHAR2 DEFAULT NULL
  ) RETURN DATE AS
    v_format VARCHAR2(50);
    v_clean_string VARCHAR2(100);
  BEGIN
    v_clean_string := standardize_date_string(p_string);

    -- Use provided format or detect it
    v_format := NVL(p_format, detect_format(v_clean_string));

    IF v_format IS NULL THEN
      RAISE_APPLICATION_ERROR(-20001,
        'Cannot detect format for: ' || p_string);
    END IF;

    RETURN TO_DATE(v_clean_string, v_format);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -1830 THEN
        -- Try truncating the string
        RETURN TO_DATE(SUBSTR(v_clean_string, 1, LENGTH(v_format)), v_format);
      ELSE
        RAISE;
      END IF;
  END smart_to_date;

  PROCEDURE validate_date_string(
    p_string VARCHAR2,
    p_format OUT VARCHAR2,
    p_is_valid OUT BOOLEAN
  ) AS
    v_date DATE;
  BEGIN
    p_format := detect_format(p_string);

    IF p_format IS NOT NULL THEN
      BEGIN
        v_date := smart_to_date(p_string, p_format);
        p_is_valid := TRUE;
      EXCEPTION
        WHEN OTHERS THEN
          p_is_valid := FALSE;
      END;
    ELSE
      p_is_valid := FALSE;
    END IF;
  END validate_date_string;

END date_converter;
/

2. Data Validation and Cleansing

Create Validation Framework

-- Create table for date format rules
CREATE TABLE date_format_rules (
  rule_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  table_name VARCHAR2(30),
  column_name VARCHAR2(30),
  expected_format VARCHAR2(50),
  allow_time CHAR(1) DEFAULT 'Y',
  allow_null CHAR(1) DEFAULT 'Y',
  active CHAR(1) DEFAULT 'Y',
  CONSTRAINT uk_format_rules UNIQUE (table_name, column_name)
);

-- Insert rules
INSERT INTO date_format_rules (table_name, column_name, expected_format)
VALUES ('ORDERS', 'ORDER_DATE', 'YYYY-MM-DD HH24:MI:SS');

-- Create validation procedure
CREATE OR REPLACE PROCEDURE validate_date_columns AS
  v_invalid_count NUMBER;
  v_sql VARCHAR2(4000);
BEGIN
  FOR rule IN (
    SELECT * FROM date_format_rules WHERE active = 'Y'
  ) LOOP
    -- Check for format violations
    v_sql := 'SELECT COUNT(*) FROM ' || rule.table_name ||
             ' WHERE ' || rule.column_name || ' IS NOT NULL' ||
             ' AND LENGTH(' || rule.column_name || ') != LENGTH(''' ||
             rule.expected_format || ''')';

    EXECUTE IMMEDIATE v_sql INTO v_invalid_count;

    IF v_invalid_count > 0 THEN
      DBMS_OUTPUT.PUT_LINE('WARNING: ' || rule.table_name || '.' ||
                          rule.column_name || ' has ' || v_invalid_count ||
                          ' records with incorrect format');
    END IF;
  END LOOP;
END;
/

-- Create cleansing job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'CLEANSE_DATE_STRINGS',
    job_type => 'PLSQL_BLOCK',
    job_action => '
      BEGIN
        UPDATE import_table
        SET date_column = date_converter.standardize_date_string(date_column)
        WHERE date_column IS NOT NULL
          AND date_column != date_converter.standardize_date_string(date_column);
        COMMIT;
      END;',
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled => TRUE
  );
END;
/

3. Error Handling and Monitoring

Implement Comprehensive Error Handling

-- Create error logging for date conversions
CREATE TABLE date_conversion_errors (
  error_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  error_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  table_name VARCHAR2(30),
  column_name VARCHAR2(30),
  original_value VARCHAR2(100),
  attempted_format VARCHAR2(50),
  error_message VARCHAR2(4000),
  resolved CHAR(1) DEFAULT 'N'
);

-- Create wrapper function with error logging
CREATE OR REPLACE FUNCTION safe_date_convert(
  p_string VARCHAR2,
  p_format VARCHAR2,
  p_table VARCHAR2 DEFAULT NULL,
  p_column VARCHAR2 DEFAULT NULL
) RETURN DATE AS
  v_date DATE;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  v_date := TO_DATE(p_string, p_format);
  RETURN v_date;
EXCEPTION
  WHEN OTHERS THEN
    -- Log the error
    INSERT INTO date_conversion_errors (
      table_name, column_name, original_value,
      attempted_format, error_message
    ) VALUES (
      p_table, p_column, p_string, p_format, SQLERRM
    );
    COMMIT;

    -- Return NULL or raise based on requirements
    RETURN NULL;
END;
/

Prevention Strategies

1. Input Validation

-- Add check constraints
ALTER TABLE orders
ADD CONSTRAINT chk_order_date_format
CHECK (REGEXP_LIKE(order_date, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$'));

-- Create trigger for validation
CREATE OR REPLACE TRIGGER validate_date_format
BEFORE INSERT OR UPDATE ON import_table
FOR EACH ROW
DECLARE
  v_test_date DATE;
BEGIN
  IF :NEW.date_string IS NOT NULL THEN
    -- Validate format
    BEGIN
      v_test_date := TO_DATE(:NEW.date_string, 'YYYY-MM-DD HH24:MI:SS');
    EXCEPTION
      WHEN OTHERS THEN
        -- Try to fix common issues
        :NEW.date_string := date_converter.standardize_date_string(:NEW.date_string);

        -- Retry conversion
        v_test_date := date_converter.smart_to_date(:NEW.date_string);
    END;
  END IF;
END;
/

2. Application Best Practices

// Java example with proper date handling
public class DateFormatHandler {

    private static final SimpleDateFormat[] FORMATS = {
        new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"),
        new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"),
        new SimpleDateFormat("yyyy-MM-dd"),
        new SimpleDateFormat("dd/MM/yyyy"),
        new SimpleDateFormat("MM/dd/yyyy")
    };

    public static Date parseFlexibleDate(String dateString) {
        if (dateString == null || dateString.trim().isEmpty()) {
            return null;
        }

        // Clean the string
        dateString = dateString.trim();

        // Remove common suffixes
        dateString = dateString.replaceAll("\\.\\d{3,6}$", "");  // Milliseconds
        dateString = dateString.replaceAll(" [A-Z]{3,4}$", "");   // Timezone
        dateString = dateString.replaceAll("T", " ");             // ISO 8601

        // Try each format
        for (SimpleDateFormat format : FORMATS) {
            try {
                return format.parse(dateString);
            } catch (ParseException e) {
                // Try next format
            }
        }

        throw new IllegalArgumentException("Unparseable date: " + dateString);
    }

    public static String formatForOracle(Date date) {
        SimpleDateFormat oracleFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return oracleFormat.format(date);
    }
}

Related Errors

• ORA-01843 - Not a valid month
• ORA-01847 - Day of month must be between 1 and last day of month
• ORA-01858 - Non-numeric character found where numeric expected
• ORA-01861 - Literal does not match format string

Quick Reference

Emergency Response Steps

1. ✓ Identify the input string and format mask
2. ✓ Check string length vs format length
3. ✓ Look for extra characters (time, timezone, milliseconds)
4. ✓ Adjust format mask to match input
5. ✓ Clean or truncate input string if needed
6. ✓ Test with corrected format

Quick Commands

-- Check string length
SELECT LENGTH('2024-01-15 14:30:45') FROM dual;

-- Truncate to date only
SELECT TO_DATE(SUBSTR('2024-01-15 14:30:45', 1, 10), 'YYYY-MM-DD') FROM dual;

-- Handle time component
SELECT TO_DATE('2024-01-15 14:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- Remove trailing spaces
SELECT TO_DATE(TRIM('2024-01-15  '), 'YYYY-MM-DD') FROM dual;

-- Use TIMESTAMP for fractional seconds
SELECT TO_TIMESTAMP('2024-01-15 14:30:45.123', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM dual;

Prevention Guidelines

• Match format exactly - Ensure format accounts for all characters
• Validate input - Check string format before conversion
• Standardize formats - Use consistent date formats across application
• Handle edge cases - Account for timezones, milliseconds
• Clean input data - Remove extra spaces and characters
• Document formats - Clearly specify expected date formats