Often in PostgreSQL you may want to use the LIKE operator to select rows where a date field matches a particular pattern.
You can use the following basic syntax to do so:
SELECT *
FROM products
WHERE date_sold::text LIKE '2024-09-%';
This particular example will select all rows from the table named products where the date in the date_sold column starts with the pattern 2024-09.
Notice that we first cast the date_sold field to text so that we can use the LIKE operator to match a specific string pattern.
The following example shows how to use this syntax in practice.
Example: How to Use LIKE Operator with Date Field in PostgreSQL
Suppose that we create the following table named products that contains information about when various products were sold by some company:
-- create table named 'products'
CREATE TABLE products (
productID INTEGER PRIMARY KEY,
name TEXT,
date_sold DATE
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A', '2024-07-11');
INSERT INTO products VALUES (0002, 'A', '2024-07-12');
INSERT INTO products VALUES (0003, 'B', '2024-08-10');
INSERT INTO products VALUES (0004, 'B', '2024-09-15');
INSERT INTO products VALUES (0005, 'C', '2024-09-18');
INSERT INTO products VALUES (0006, 'C', '2024-09-22');
INSERT INTO products VALUES (0007, 'C', '2024-10-14');
INSERT INTO products VALUES (0008, 'C', '2024-10-30');
-- view all rows in table
SELECT * FROM products;
productid | name | date_sold
-----------+------+------------
1 | A | 2024-07-11
2 | A | 2024-07-12
3 | B | 2024-08-10
4 | B | 2024-09-15
5 | C | 2024-09-18
6 | C | 2024-09-22
7 | C | 2024-10-14
8 | C | 2024-10-30
Suppose that we would like to only select the rows where the date in the date_sold column starts with the pattern 2024-09, i.e. the product was sold in September of 2024.
We can use the following syntax to do so:
SELECT *
FROM products
WHERE date_sold::text LIKE '2024-09-%';
productid | name | date_sold
-----------+------+------------
4 | B | 2024-09-15
5 | C | 2024-09-18
6 | C | 2024-09-22
Notice that the resulting table only contains the rows where the date in the date_sold column starts with the pattern 2024-09, just as we specified.
Feel free to use similar syntax with the LIKE operator to select rows from your own table that contain a specific pattern in a date field.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Use LIKE Operator with Multiple Values
PostgreSQL: How to Use Case-Insensitive LIKE Operator
PostgreSQL: How to Find Position of Character in String
