Query Listdates from the last 30 days

JJK 20 Reputation points


I have a table. Inside are the Listdates.

  • 2023-08-20
  • 2023-09-12
  • 2023-10-23
  • 2023-10-15
  • 2023-11-23
  • 2023-10-09
  • 2023-11-06

I use this query to get Listdates from the last 30 days.

FROM test 
WHERE  DATEDIFF(day,ListDate,GETDATE()) <=30;

But I got 2023-11-23, which should not be here.


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,653 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,463 questions
0 comments No comments
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,041 Reputation points Microsoft Vendor

    Hi @JJK

    You can try this.

    create table test(ListDate datetime);
    insert into test values
    select * from test 
    where ListDate >= DATEADD(day,-30,GETDATE()) and ListDate <= getdate();


    User's image

    Best regards,

    Percy Tang

    2 people found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Wilko van de Velde 1,806 Reputation points

    Hi JJK,

    The function datediff will also return negative values. Look at the result of this query:

    select *, DATEDIFF(day,ListDate,GETDATE()) from test 


    User's image

    As you can see 2023-11-23, has a datediff less then 30 so it will turn up in your result.

    You can fix it by using between in the where:

    SELECT * 
    FROM test 
    WHERE  DATEDIFF(day,ListDate,GETDATE()) between 0 and 30;

    Or use the query of @PercyTang-MSFT

    Kind regards,


    Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.
    0 comments No comments

  2. JJK 20 Reputation points

    Thank you both!

    0 comments No comments