Often in PostgreSQL you may want to convert a column from varchar to date.
You can use the following basic syntax to do so:
ALTER TABLE products ALTER COLUMN date_sold TYPE DATE
using to_date(date_sold, 'YYYY-MM-DD');
This particular example will convert the date_sold column in the athletes table from a varchar column to a date column, specifying that the date should use ‘YYYY-MM-DD’ as the format.
The following example shows how to use this syntax in practice.
Example: How to Convert Column from varchar to Date in PostgreSQL
Suppose that we create the following table named products that contains information about when various products were sold at some retail store.
-- create table named 'products'
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
date_sold VARCHAR (10)
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A0045BH', '2024-04-01');
INSERT INTO products VALUES (0002, 'A00933R', '2024-06-02');
INSERT INTO products VALUES (0003, 'BRRHHYL', '2024-07-03');
INSERT INTO products VALUES (0004, 'BR33503', '2024-09-04');
INSERT INTO products VALUES (0005, 'CHYYGG4', '2024-10-05');
INSERT INTO products VALUES (0006, 'C743559', '2024-11-06');
INSERT INTO products VALUES (0007, 'HTT56HH', '2024-12-07');
-- view all rows in table
SELECT * FROM products;
id | name | date_sold
----+---------+------------
1 | A0045BH | 2024-04-01
2 | A00933R | 2024-06-02
3 | BRRHHYL | 2024-07-03
4 | BR33503 | 2024-09-04
5 | CHYYGG4 | 2024-10-05
6 | C743559 | 2024-11-06
7 | HTT56HH | 2024-12-07
The date_sold column is currently a varchar but suppose that we would like to convert it to a date.
We can use the following syntax to do so:
ALTER TABLE products ALTER COLUMN date_sold TYPE DATE
using to_date(date_sold, 'YYYY-MM-DD');
We can then use the following syntax to display the data type of each column in the products table:
-- view data type of each column in table
SELECT
column_name,
data_type
FROM
information_schema.columns
WHERE
table_name = 'products';
column_name | data_type
-------------+-----------
id | integer
date_sold | date
name | text
From the output we can see that the date_sold column now has a data type of date.
Feel free to use similar syntax with the ALTER COLUMN statement to convert a varchar column to a date column in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Extract Month Name from Date
PostgreSQL: How to Generate Date Series Between Two Dates
