Extract week number from character varying column where dates are in float values as well as in datetimestamp as well
You can use a CASE expression with a regex to test if the string is formatted like an ISO date or not:

SELECT closure_date, 
       case 
         when closure_date ~ '[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9:]{8}' 
           then extract(week from closure_date::timestamp)
         else
           extract('week' from timestamp '1899-12-30' + (interval '1 day' * cast(closure_date as double precision))) 
        end as closure_week
FROM &lt;table_name&gt;


The ~ operator compares the closure_date with the regular expression to see if it matches that pattern.

Note that if you have different formats, this could still fail and you need to add more checks.



If you have the chance, you should really change that column to be a timestamp rather than keeping this badly formatted string.