How to convert date strings to timestamp without knowing the date format

Question

I am trying to write a query to insert a value into a timestamp with no timezone data type field. The value is coming from CSV file.

The version I am working with is PostgreSQL 8.1.21.

The CSV file upload is done by the client and it has a date column. The date sometimes comes as '28-Sep-13' and sometimes as '28/09/2013' formats.

I tried to use the following to cast the string into timestamp: str_date::timestamp.

This works fine if str_date is something like '28-Sep-13' but it won't work if the incoming date has the format '28/09/2013', when this error occurs:

ERROR: date/time field value out of range: "28/09/2013"  
HINT:  Perhaps you need a different "datestyle" setting

Basically the client keeps changing the date format in the uploaded CSV file.
Is there a way to convert the date strings into timestamp depending on its actual format?

Answer 1

You need to set your datestyle to "ISO, DMY". It is set to "ISO, MDY" by default, and would cause your example to fail:

> show datestyle;

 DateStyle 
-----------
 ISO, MDY
(1 row)

> select '28-Sep-13'::date;
    date    
------------
 2013-09-28
(1 row)

> select '28/09/2013'::date;
ERROR:  date/time field value out of range: "28/09/2013"
LINE 1: select '28/09/2013'::date;
               ^
HINT:  Perhaps you need a different "datestyle" setting.

> set datestyle = 'ISO, DMY';
SET

> select '28-Sep-13'::date;
    date    
------------
 2013-09-28
(1 row)

> select '28/09/2013'::date;
    date    
------------
 2013-09-28
(1 row)

(examples done in PostgreSQL 9.1, but the DateStyle setting and associated behavior are ancient, so should work fine)