Query Listdates from the last 30 days

Question

Query Listdates from the last 30 days

JJK 20

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.

0 comments

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Anonymous

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:

User's image

Best regards,

Percy Tang

0 comments

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:

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 @Anonymous

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

JJK 20

Thank you both!

0 comments

Share via

Query Listdates from the last 30 days

2 additional answers

Your answer