fixts

[2026-09-23 01:58:15 CST]

CREATE OR REPLACE FUNCTION fixTS (datetimestring IN VARCHAR2)

RETURN VARCHAR2

clean_str VARCHAR2(100);

datetimevar TIMESTAMP;

BEGIN

-- Return NULL immediately if input is empty

IF datetimestring IS NULL THEN

RETURN NULL;

END IF;

-- Trim spaces and compress multiple spaces into one

clean_str := REGEXP_REPLACE(TRIM(datetimestring), '\s+', ' ');

-- Attempt parsing using a fallback sequence of matching formats

BEGIN

datetimevar := TO_TIMESTAMP(clean_str, 'MM/DD/YYYY HH12:MI:SS AM');

EXCEPTION WHEN OTHERS THEN

BEGIN

datetimevar := TO_TIMESTAMP(clean_str, 'MM/DD/YYYY HH24:MI:SS');

EXCEPTION WHEN OTHERS THEN

BEGIN

datetimevar := TO_TIMESTAMP(clean_str, 'YYYY/MM/DD HH24:MI:SS');

EXCEPTION WHEN OTHERS THEN

BEGIN

datetimevar := TO_TIMESTAMP(clean_str, 'YYYY-MM-DD HH24:MI:SS');

EXCEPTION WHEN OTHERS THEN

BEGIN

datetimevar := TO_TIMESTAMP(clean_str, 'YYYY-MM-DD HH24:MI');

EXCEPTION WHEN OTHERS THEN

BEGIN

datetimevar := TO_TIMESTAMP(clean_str, 'YYYY-MM-DD');

EXCEPTION WHEN OTHERS THEN

BEGIN

datetimevar := TO_TIMESTAMP(clean_str, 'MM/DD/YYYY');

EXCEPTION WHEN OTHERS THEN

-- Fallback if no format matches

RETURN 'INVALID_FORMAT';

END; END; END; END; END; END; END;

-- Convert the valid timestamp back into your preferred string format

RETURN TO_CHAR(datetimevar, 'YYYY-MM-DD HH24:MI:SS');

END fixTS;