Often in PostgreSQL you may want to check if a particular string is a valid date.
You can create the following function in PostgreSQL to do so:
create function check_date(s varchar) returns boolean
language plpgsql
as $$
begin
perform s::date;
return true;
exception when others then
return false;
end;
$$;
This particular function named check_date() accepts a string as an argument and will return either true or false as a result to indicate whether or not the string is a valid date.
The following example shows how to use this syntax in practice.
Example: How to Check if String is a Date in PostgreSQL
Suppose that we create the following table named sales that contains information about total sales made by some retail store on various dates:
-- create table named 'sales'
CREATE TABLE sales (
date_sold TEXT,
amount INT
);
-- add rows to the table
INSERT INTO sales VALUES ('2024-10-15', 54);
INSERT INTO sales VALUES ('10/25/2024', 54);
INSERT INTO sales VALUES ('October 10, 2024', 70);
INSERT INTO sales VALUES ('The year 2022', 77);
INSERT INTO sales VALUES ('2023-40-45', 71);
INSERT INTO sales VALUES ('May 41, 2024', 50);
-- view all rows in table
SELECT * FROM sales;
date_sold | amount
------------------+--------
2024-10-15 | 54
10/25/2024 | 54
October 10, 2024 | 70
The year 2022 | 77
2023-40-45 | 71
May 41, 2024 | 50
Suppose that we would like to check if each string in the date_sold column is a valid date.
We can create the following function named check_date() to do so:
create function check_date(s varchar) returns boolean
language plpgsql
as $$
begin
perform s::date;
return true;
exception when others then
return false;
end;
$$;
We can then run the following query to check if each string in the date_sold column is a valid date:
SELECT *,
check_date(date_sold)
FROM sales;
date_sold | amount | check_date
------------------+--------+------------
2024-10-15 | 54 | t
10/25/2024 | 54 | t
October 10, 2024 | 70 | t
The year 2022 | 77 | f
2023-40-45 | 71 | f
May 41, 2024 | 50 | f
The new column named check_date returns either true or false to indicate whether or not the corresponding string in the date_sold column is a valid date.
You can use similar syntax to check if a string is a date in your own table in PostgreSQL.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Calculate Difference Between Dates in Days
PostgreSQL: How to Calculate Difference Between Dates in Weeks
PostgreSQL: How to Calculate Difference Between Dates in Months
