I would like to create a function to validate a field from an import table and ensure the field is in 'DateTime' format. I am not entirely sure how to do this and would appreciate any help?

Function Name: val_datetime
Field: Created Date
Format: dd/mm/yyyy hh:mm:ss

Thank you for any help.


Try something like:

to_timestamp('21/12/2008 12:34:23','dd/mm/yyyy HH:MI:SS')

I assume you want to catch any exception and return null instead, so something like:

create or replace function my_to_timestamp(arg text)
returns timestamp language plpgsql
as $$
        return to_timestamp(arg, 'dd/mm/yyyy HH:MI:SS');
    exception when others then
        return null;
end $$;


postgres=# select my_to_timestamp('21/12/2008 12:34:14');
 2008-12-21 00:34:14
(1 row)

postgres=# select my_to_timestamp('21/12/2008 12:x:14');

(1 row)

You can find all valid/invalid rows as:

with t(s) as ( values ('21/12/2008 12:34:14')
                    , ('21/12/2008 12:Tx:14')
select s from T
where my_to_timestamp(s) is [not] null;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.