Search code examples
sql-serverdatewhere-clausedate-arithmetic

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

I have sample data as below:

Date Index
26-07-2022 26
26-06-2022 23
24-07-2022 12
19-06-2022 16
26-04-2022 01
26-05-2022 10
26-07-2022 12

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:

Date Index
26-07-2022 26
26-06-2022 23
26-04-2022 01
26-05-2022 10
26-07-2022 12

Do anybody know how can I achieve this. Thanks.

Solution

  • 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.