Query Listdates from the last 30 days

Question

Hi.

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.

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

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

Help.

Answer 1

Hi @JJK

You can try this.

create table test(ListDate datetime);
insert into test values
('2023-08-20'),
('2023-09-12'),
('2023-10-23'),
('2023-10-15'),
('2023-11-23'),
('2023-10-09'),
('2023-11-06');

select * from test 
where ListDate >= DATEADD(day,-30,GETDATE()) and ListDate <= getdate();

Output:

Best regards,

Percy Tang

Answer 2

Hi JJK,

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

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

Result:

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,

Wilko

---

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

Answer 3

Thank you both!