Extract week number from character varying column where dates are in float values as well as in datetimestamp as well I have a table in which the closure_date which is represented as a character varying column. Some of the dates are saved as "43684.5708564815" and also as "2019-05-24 18:51:17". Now I need to extract the week number from the column. I tried the following the query :

 SELECT closure_date, 
 extract('week' from timestamp '1899-12-30' + interval '1 day' * cast(closure_date as  double 
 precision)) as closure_week,
 FROM &lt;table_name&gt;
 LIMIT 10000


This query runs fine for values like  43684.5708564815 however result into an error while trying to extract the week_number from "2019-05-24 18:51:17" stating :

 ERROR:  invalid input syntax for type double precision: "2019-05-24 18:51:17"
 SQL state: 22P02


How to handle the error? Any suggestions?
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.