Postgres String to Date EXAMPLE 10Apr77 to 10/04/1977

Question

Good afternoon all,

I'm fairly new to PostgreSQL, so apologies in advance if my question is below the average.

I'm receiving some data in a csv flay file format and is nicely being insert in to a staging table for me to preview the data. On of the things i have an problem with is the data field is store as a string value. For example, 10Apr96 - 01Jan85

I've wrote the below query to see if could break the string up and concat back together in a date format.

Is there an easier way to achieve what I'm doing ?

select 
concat(cast(substring(datetext,1,2) as varchar(2)),'/',case 
when cast(substring(datetext,3,3) as varchar(3)) = 'Jan' then '01'
when cast(substring(datetext,3,3) as varchar(3)) = 'Feb' then '02'
when cast(substring(datetext,3,3) as varchar(3)) = 'Mar' then '03'
when cast(substring(datetext,3,3) as varchar(3)) = 'Apr' then '04'
when cast(substring(datetext,3,3) as varchar(3)) = 'May' then '05'
when cast(substring(datetext,3,3) as varchar(3)) = 'Jun' then '06'
when cast(substring(datetext,3,3) as varchar(3)) = 'Jul' then '07'
when cast(substring(datetext,3,3) as varchar(3)) = 'Aug' then '08'
when cast(substring(datetext,3,3) as varchar(3)) = 'Sep' then '09'
when cast(substring(datetext,3,3) as varchar(3)) = 'Oct' then '10'
when cast(substring(datetext,3,3) as varchar(3)) = 'Nov' then '11'
when cast(substring(datetext,3,3) as varchar(3)) = 'Dec' then '12'
end,'/',cast(substring(datetext,6,2) as varchar(2))) as dt
from tbl_loading_horses_tmp

Answer 1

You can use the to_date() function, which takes a format string of how to interpret the input and returns a date value which is what you should be storing in your DB.

SELECT to_date('10Apr96', 'DDMonYY');
  to_date   
------------
 1996-04-10
(1 row)

To convert that back to a string in a different format when retrieving for display, use the to_char() function documented on the same page.