CODEWITHSUNDEEP
postgresqldate
rshar
rshar

Reputation: 1477

Change the column type to 'Date' from 'Character varying' in PostgreSQL 11.0

I have a postgreSQL 11.0 table with dates in below format, but the column type 'character varying'.

id        primary_completion_date 
0         December2019
1         April2020
2         September2021
3         September2022
4         December2021

Is it possible to convert the column type to 'date'. When I try to convert it to date, It changes the column content to below:

id        primary_completion_date
0         12-2019-01
1         04-2020-01
2         09-2021-01
3         09-2022-01
4         12-2021-01

I am using following statement in my script.

alter table tbl alter primary_completion_date type date using to_date(primary_completion_date, 'MonthYYYY')

How can I retain the column content as the input but change the column type to 'Date'.

Upvotes: 0

Views: 497

Answers (1)

user330315
user330315

Reputation:

How can I retain the column content as the input but change the column type to 'Date'*"

You can't because December2019 is not a valid value for a date column.

But you can always format the date value when you retrieve it:

select id, to_char(primary_completion_date, 'MonthYYYY') as formatted_completion_date
from the_table

Upvotes: 1

Related Questions