I know this is probably very basic, but I'm trying to create a calculated field that subtracts X number of from Current Date, based off of the value of another field:

     WHEN DaysPD = 180 THEN 6
     WHEN DaysPD = 360 THEN 12
 END AS MonthsPD

CASE WHEN RateCode = 'AC' THEN RateResetDate - MonthsPD 

In this example, if the RateResetDate = '03/08/2019' and MonthsPD = 6, then I would want the calculation to be '03/08/2019' - 6 Months = '10/8/2018'


You can use the DATEADD function to do that:

CASE WHEN [RateCode] = 'AC'
            CASE WHEN DaysPD = 30 THEN -1 
                 WHEN DaysPD = 180 THEN -6 
                 WHEN DaysPD = 360 THEN -12 
    END AS [AdjustedDate]  

Alternatively, you can just pass in the days if you want:

CASE WHEN [RateCode] = 'AC'
      THEN DATEADD(DAY, -DaysPD, [RateResetDate]) 
    END AS [AdjustedDate]  
  • 1
    Wow, I was really overthinking that one. That works perfect, Thank you!! – Mike Jones Mar 8 '19 at 21:54
  • @MikeJones no problem! – Mr.Brownstone Mar 8 '19 at 21:55
  • 1
    Mind that if DaysPD value is neither 30, 180 or 360, you'll get an implicit ELSE NULL in the CASE expression and NULL as a result for AdjustedDate. You may prefer to add an ELSE 0 for example, to cover those cases. – ypercubeᵀᴹ Mar 9 '19 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.