PostgreSQL Converting Two String Date Columns (Year,Month) to One Column (YYYY/MM/DD)

Question

I have a PostgreSQL table with a Month attribute and Year attribute. They are both formatted as Strings... that is: September, October, November, December and '2016','2017','2018'. How can I combine these month and year columns into a Date column that has the format YYYY/MM/01 indicating the first day of the month.

S-Man S-Man · Accepted Answer · 2018-12-18T14:35:44

demo:db<>fiddle

First step: Generating a date with to_date(): Identifier Month detects the long month name

SELECT to_date(year || '-' || month || '-01', 'yyyy-Month-dd')

Second step: Generating your expected date format with to_char()

SELECT 
    to_char(
        to_date(year || '-' || month || '-01', 'yyyy-Month-dd'),
        'yyyy/MM/dd'
    )
FROM mydates

Postgres date functions

PostgreSQL Converting Two String Date Columns (Year,Month) to One Column (YYYY/MM/DD)

1 Answers