SQL Server 2012 – How to Subtract Days From Current Date

datedatetimesql-server-2012

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:

CASE WHEN DaysPD = 30 THEN 1
     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'

Best Answer

You can use the DATEADD function to do that:

CASE WHEN [RateCode] = 'AC'
      THEN DATEADD(MONTH, 
            CASE WHEN DaysPD = 30 THEN -1 
                 WHEN DaysPD = 180 THEN -6 
                 WHEN DaysPD = 360 THEN -12 
             END,
           [RateResetDate]) 
    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]
Related Question