Detect date format using CASE expression-CodePudding

How to get NULL value if date format is not matched in below query.

create table hiredate (ename character varying (50), hiredate text);

INSERT INTO hiredate (ename,hiredate) VALUES ('KING','1991-04-01'), ('BLAKE','2009-02-11'), ('CLARK','20020101'), ('SMITH','21/12/1992'), ('GLEN','13-8-1992'), ('JOHN','10-15-1994'), ('DEL','28/07202');

SELECT ename,hiredate, TO_DATE(
         hiredate,
         CASE 
           WHEN hiredate LIKE '____-__-__' THEN 'YYYY-MM-DD' 
           WHEN hiredate LIKE '________' THEN 'YYYYMMDD' 
           WHEN hiredate LIKE '__-__-____' THEN 'MM-DD-YYYY' 
           WHEN hiredate LIKE '__/__/____' THEN 'DD/MM/YYYY' 
           WHEN hiredate LIKE '__-_-____' THEN 'DD-MM-YYYY' 
           WHEN hiredate LIKE '___-__-____' THEN 'Mon-DD-YYYY' 
         END 
       ) updated_hiredate
FROM hireDate;

CodePudding user response：

You can create a function like this

create or replace function timestamp_from_string(_str text)
returns timestamp language plpgsql
as $$
declare 
_format varchar;
begin
    begin   
         CASE 
           WHEN _str LIKE '____-__-__' THEN _format ='YYYY-MM-DD' ;
           WHEN _str LIKE '________' THEN _format ='YYYYMMDD' ;
           WHEN _str LIKE '__-__-____' THEN _format ='MM-DD-YYYY'; 
           WHEN _str LIKE '__/__/____' THEN _format ='DD/MM/YYYY' ;
           WHEN _str LIKE '__-_-____' THEN _format ='DD-MM-YYYY' ;
           WHEN _str LIKE '___-__-____' THEN _format ='Mon-DD-YYYY'; 
               END case;
            return to_date (_str,_format)::timestamp;
    exception when others then
        return null;
    end;
end $$;

and then use a select like this:

    SELECT 
        ename,
        hiredate,
        timestamp_from_string(hiredate) as hiredate_timestamp
FROM hireDate;