How to select records of same day as today from all months and years

Question

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.

Let's make this more clear. You want to select rows with the same day as "today" for all prior months within the same year? So everything >= Jan 1 2022, <= getdate() and where DAY() returns 26. — SMor, Jul 26 '22 at 11:26
https://stackoverflow.com/users/7926183/smor I want to select records of same day as today for all prior months for all years. — Dizzy, Jul 26 '22 at 11:39
One way is to write a function which will construct the date for each of the priori months based on todays date. This has issues for example if the date is 30 , Feb month will have an invalid date. Another way is to use the built in function refer following link https://stackoverflow.com/questions/5425627/sql-query-for-todays-date-minus-two-months — A Modgil, Jul 26 '22 at 11:51

score 0 · Accepted Answer · answered Jul 26 '22 at 11:54

0

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.

answered Jul 26 '22 at 11:54

forpas

1 Answers1