Convert date to specific format using Postgresql

Question

I am needing to convert records where the TEXT values look like this using Postgresql:

26-AUG-2015 
to:  
2015-08-26

I'm not sure what version of Postgresql exists on the vendor server but I tried to do a select statement using:

SELECT to_char(sle.log_field1, 'YYYY-MM-DD')
FROM student_log_entires sle;

But I'm getting this error:

Error: SQL Error: SQLSTATE[42883]: Undefined function: 7 ERROR: function to_char(text, unknown) does not exist LINE 25: AND to_char(sle.log_field1, 'YYYY-MM-DD') >=... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

I did also try:

SELECT to_date(sle.log_field1, 'YYYY-MM-DD')
FROM student_log_entries sle 

But I got this error:

Error: SQL Error: SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid value "[E] " for "YYYY" DETAIL: Value must be an integer. Query: SELECT to_date(sle.log_field1, 'YYYY-MM-DD') FROM student_log_entries sle 

Any suggestions/direction would be appreciated. Thanks.

Answer 1

This assumes that lc_time is set to English:

SELECT to_char(to_date('26-AUG-2015', 'DD-MON-YYYY'), 'YYYY-MM-DD');
  to_char
------------
 2015-08-26
(1 row)

Answer 2

You can convert the value to a date and then back to a string:

select to_char(to_date('26-AUG-2015', 'DD-MON-YYYY'), 'YYYY-MM-DD')

Under many circumstances, processing the value as a date is probably sufficient.

Your approach doesn't work because the value is apparently already stored as a string, so converting it back to a string with a date format doesn't make sense.

EDIT:

You may be able to get by using a simple regular expression:

select (case when col ~ '^[0-9]{2}[-][A-Z]{3}[-][0-9]{4}$' 
             then to_char(to_date('26-AUG-2015', 'DD-MON-YYYY'), 'YYYY-MM-DD')
        end)

Of course, if the formatting errors are more subtle, then a more complex regular expression would be needed.