Automatic query of data in recent months

potQ-8127 40 Reputation points


I have a large data table, which is updated every day. It has many columns, one of which is Update_ Date, which will display the date of data update.

I can use the where criteria to filter the data of the last three months, but this is a bit troublesome. It needs to be entered manually, and sometimes I need to view the data of the last six or eight months, which is not fixed.

Is there a convenient query that can realize this function?


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,768 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 5,386 Reputation points Microsoft Vendor

    Hi @potQ-8127

    You can try this query.

    create table test(id int,date datetime);
    insert into test values
    ;with CTE as(
      select *,dense_rank()over(order by eomonth(date) desc) as num from test)
    select * from CTE where num < 4;--You can modify here to change the month of the query

    The eomonth function returns the last day of the month containing the specified date.


    User's image

    Best regards,

    Percy Tang

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Newest
  1. Olaf Helper 28,781 Reputation points

    It needs to be entered manually

    Entered where?

    I need to view the data of the last six or eight months, which is not fixed.

    Not fix is a problem.

    For a fix date range you can use DATEADD (Transact-SQL) to "add" (substract) e.g. six month to the current date.

    0 comments No comments