I have sample data as below:

I want to select data of latest day from each month. For example if today's date is 26-07-2022 then I want to select all records where date is 26th.

So my output should look like below:

Do anybody know how can I achieve this. Thanks.

CodePudding user response：

Assuming that the data type of the column [Date] is DATE, use the function DAY():

SELECT *
FROM tablename
WHERE DAY([Date]) = DAY(GETDATE())
  AND [Date] <= GETDATE(); -- you may remove this if it is not needed

See the demo.