Subtract column of integers from Date column in SQL Server-CodePudding

I am trying to subtract a column of integers from a Date format column in T-SQL within a case statement however there's get a conversion error. I understand the reason for the error but not how to resolve it.

CASE 
    WHEN EVT = 'x' 
        THEN DATEADD(DAY, -[DATE COLUMN], [INTEGER COLUMN]) 
        ELSE [DATE COLUMN 2] - [INTEGER COLUMN] 
END AS y

Have tried it with and without the square brackets.

Any suggestions please?

CodePudding user response：

If you consult the [official MS Documentation on DATEADD, you'll see the proper syntax:

DATEADD (datepart, number, date)

So I would guess in your case, it should be:

CASE 
    WHEN EVT = 'x' 
        THEN DATEADD(DAY, -1 * [INTEGER COLUMN], [DATE COLUMN]) 
        ELSE DATEADD(DAY, -1 * [INTEGER COLUMN], [DATE COLUMN 2])
END AS y

assuming you want to subtract the value of the integer column as days from either of the two date columns.

CodePudding user response：

If both columns are datetime columns - and you want to subtract days:

iif(EVT = 'x', [DATE COLUMN], [DATE COLUMN2]) - [INTEGER COLUMN]

As a CASE expression (which IIF will be converted to):

CASE WHEN EVT = 'x' THEN [DATE COLUMN] ELSE [DATE COLUMN2] END - [INTEGER COLUMN]

If your date columns are date, datetime2, datetimeoffset data types - then you must use DATEADD:

DATEADD(day, -[INTEGER COLUMN], IIF(EVT = 'x', [DATE COLUMN], [DATE COLUMN2]))