Where condition to filter SQL query to the first day of each month-CodePudding

I have a SQL query that contains multiple records for each day of the month, over a number of years. Can someone help me out to filter the query that will only return the first day of each month. And also as soon as a new month starts, it is automatically added to the filter? Really appreciate any input.

Thanks

CodePudding user response：

You didn't tag your backend but most databases have the same or similar datepart() function for extracting parts of date. ie:

select * from myTable where datepart('day', theDateColumn) = 1;

CodePudding user response：

In SQL you can query a date using the LIKE statement.

Assuming that you have a field named 'randomDate' in 'mytable' where you want to perform the query, and that this is a DATE type, you could do something like the following:

CREATE TABLE mytable ( id int, randomDate date);

INSERT INTO mytable (id, randomDate) 
VALUES
(1, '2022-12-01'),
(2, '2022-12-02');

SELECT * FROM mytable WHERE randomDate LIKE '';