CODEWITHSUNDEEP
postgresqldate
Pablo
Pablo

Reputation: 139

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

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.

Upvotes: 1

Views: 1596

Answers (1)

S-Man
S-Man

Reputation: 23676

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

Upvotes: 2

Related Questions