CODEWITHSUNDEEP
postgresqldatetime
Derek Lee
Derek Lee

Reputation: 505

Postgres invalid date format

Below are some samples of date from a column in my database. Currently, these date values are stored in varchar and I am planning to set these value as timestamp in another table.

How can I set a default rules to cast all the invalid date format to a NULL value or some sort of '00/00/0000 00:00:00" format?

pickup_time 

01/01/2016 07:35:00 
00:00:00
31/12/2015
07:35:00

Expectation:

pickup_time 

01/01/2016 07:35:00 
NULL
31/12/2015 00:00:00
00/00/0000 07:35:00

Upvotes: 2

Views: 2511

Answers (1)

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

Ok, here is the basic idea, so you need to set a if/else with regex (or something to figure out the pattern of the string) per each case you want to handle.

create or replace function my_super_converter_to_timestamp(arg text)
returns timestamp language plpgsql
as $$
begin
    begin
        -- if(arg like '00:00:00') do something ...
        return arg::timestamp;
    exception when others then
        return null;
    end;
end $$;

Upvotes: 2

Related Questions