Search code examples
t-sqlms-accesscaseiif

Transacting MS Access query (using IIF() and DATESERIAL()) into T-SQL

I'm trying to convert some MS Access queries to T-SQL to use in SSIS (basically converting Access db to SQL server 2008) and I'm having trouble converting an IIF() statement. I tried several approaches and it always resulted in an error.

The query creates a column with dates that are "original Date + 2 years if the condition is met and original Date + 1 if the condition is not met". The first part of the IIF() eliminates the case of the original year being a leap year and so the possibility of generating a non-existing date.

The original IIF() statement is:

IIf((Day(Date)=29 And Month(Date)=2),   
IIf(Desc Like "*" & "123" & "*",
    DateSerial(Year(Date)+2,Month(Date),Day(Date)-1),
    DateSerial(Year(Date)+1,Month(Date),Day(Date)-1)),  
IIf(Desc Like "*" & "123" & "*",
    DateSerial(Year(Date)+2,Month(Date),Day(Date)),
    DateSerial(Year(Date)+1,Month(Date),Day(Date)))) AS Term

So the problem is not only an IIF() statement but also DATESERIAL function. I found the solution for the DATESERIAL() function using CAST() (SQL server 2008 does not have the DATEFROMPARTS() function...).

I tried using CASE() like this:

CASE
WHEN DAY(Date)=29 AND Month(Date)=2 THEN
    CASE
        WHEN Desc LIKE "%123%" THEN
    CAST(CAST(YEAR(Date)+2 AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(Date) AS VARCHAR(2)), 2) + RIGHT('0' + CAST(DAY(Date)-1 AS VARCHAR(2)), 2) AS DATETIME )
        ELSE CAST(CAST(YEAR(Date)+1 AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(Date) AS VARCHAR(2)), 2) + RIGHT('0' + CAST(DAY(Date)-1 AS VARCHAR(2)), 2) AS DATETIME ) END

ELSE CASE
    WHEN Desc LIKE "%123%" THEN
    THEN CAST(CAST(YEAR(Date)+2 AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(Date) AS VARCHAR(2)), 2) + RIGHT('0' + CAST(DAY(Date) AS VARCHAR(2)), 2) AS DATETIME )
    ELSE CAST(CAST(YEAR(Date)+1 AS VARCHAR(4)) + RIGHT('0' + CAST(MONTH(Date) AS VARCHAR(2)), 2) + RIGHT('0' + CAST(DAY(Date) AS VARCHAR(2)), 2) AS DATETIME )END END AS Term

I also tried using COAELSCE() but with no better outcome.

I really don't know if I have made some kind of syntax error or where the problem could be.

Thank you in advance for any help.

edit: I'll add error message I'm getting: Incorrect syntax near '...'. The '...' changes as I try different approaches, sometimes its ELSE, THEN etc.

Solution

  • SQL Server's DATEADD may help to simplify things here...

     CASE WHEN (<your condition>) 
 THEN DATEADD(YEAR, 1, [OriginalDate]) 
 ELSE DATEADD(YEAR, 2, [OriginalDate])
 END

    Should also cope with leap years too.