CODEWITHSUNDEEP
sqlt-sql
user2146212
user2146212

Reputation: 93

Datediff, how to not include start date that's in the future

My current query is 

case when datediff(day, isnull(installment_duedate, due_date), getdate()) < 30 
then amount_due else 0 end as [unpaid under 30 days]

I have another 3 case that does between 30-60 days, between 60-90s and greater then 90 days.

My current problem is, my installment_duedate has future dates in it. If someone has an installment due date it would be something like 12-02-2012, 03-02-2013, 06-02-2013, 09-02-2013. If it's in the future I would like it to return as 0 for now but currently the amount is showing up in my under 30 days.

Any pointers would be greatly appreciated!

Upvotes: 0

Views: 883

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

In this line , when installment_duedate is past date , DATEDIFF returns negative value which is less than 30 and hence < 30 condition gets satisfied

datediff(day, isnull(installment_duedate, due_date), getdate()) < 30

Format

DATEDIFF(datepart,startdate,enddate)

for solution to it , see Lamak's answer below

Upvotes: 2

Lamak
Lamak

Reputation: 70648

You need to change your case to:

CASE WHEN DATEDIFF(DAY, ISNULL(installment_duedate, due_date), GETDATE()) BETWEEN 0 AND 30
     THEN amount_due ELSE 0 END AS [unpaid under 30 days]

Upvotes: 2

Related Questions