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.

Laurenz Albe · Accepted Answer · 2016-08-02 12:16:08Z

8

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)

answered Aug 2, 2016 at 12:16

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-08-03 01:26:01Z

3

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.

edited Aug 3, 2016 at 1:26

answered Aug 2, 2016 at 12:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Melinda Over a year ago

Thanks for the reply. However I got this error: Error: SQL Error: SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid value "[E" for "DD" DETAIL: Value must be an integer. Query: SELECT to_char(to_date(sle.log_field1, 'DD-MON-YYYY'), 'YYYY-MM-DD') FROM student_log_entries sle

Melinda Over a year ago

So just using the to_date function should suffice as you stated? Sorry I just want to make sure I'm clear on what you're saying. Thanks.

Laurenz Albe Over a year ago

It seens that there are entries that do not adhere to the format DD-MON-YYYY.

Vinaysingh Khetwal · Accepted Answer · 2020-07-14 12:29:07Z

1

try this,

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

answered Jul 14, 2020 at 12:29

Vinaysingh Khetwal

1311 silver badge7 bronze badges

Collectives™ on Stack Overflow

Convert date to specific format using Postgresql

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related