question

technette avatar image
technette asked

Date Range with case statement

Hi! I have the following statement in my query and I need to find a 90 day range from the given date. How can I accomplish this? CASE WHEN CLD.Desired_Ship_Date IS NOT NULL THEN CLD.Desired_Ship_Date ELSE Col.desired_ship_date END AS DesiredDate
case-statementdate-range
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
If you're looking for "Desired" date within the last 90 days... WHERE datediff(day, isNull(CLD.Desired_Ship_Date, Col.desired_ship_date), getdate()) < 90 If you're looking for a date range that covers a range of 90 days before and after "DesiredDate"... WHERE some_date between dateadd(day, -90, isNull(CLD.Desired_Ship_Date, Col.desired_ship_date)) AND dateadd(day, 90, isNull(CLD.Desired_Ship_Date, Col.desired_ship_date))
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette avatar image technette commented ·
Yes I'm looking for the range 90 day from the desired_ship_date. I tried the following: but it's picking up dates beyond 90 days WHERE datediff(day, isNull(CLD.Desired_Ship_Date, Col.desired_ship_date), getdate()) < 90
0 Likes 0 ·
KenJ avatar image KenJ commented ·
is "DesiredDate" in the future? That would make datediff evaluate to a negative number and return everything. You might check the absolute value of the difference WHERE abs(datediff(day, isNull(CLD.Desired_Ship_Date, Col.desired_ship_date), getdate())) < 90
0 Likes 0 ·
technette avatar image technette commented ·
Thank you Ken! It looks like this works. abs(datediff(day, isNull(CLD.Desired_Ship_Date, Col.desired_ship_date), getdate())) < 90
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Glad to help!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.