sqlsql-serverdateteradata-sql-assistant

Modify query with TRUNC, ADD_MONTHS, LAST_DAY from Teradata SQL so as to work on SQL Server?


I have line of code from Teradata SQL like below:

WHERE CAST(COL1 AS DATE) BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE, -6),'MM') AND LAST_DAY(ADD_MONTHS(CURRENT_DATE,-1))

And my question is how to modify above line of code so as to work also in SQL Server?


Solution

  • You can use EOMONTH to get the last day (DATE) of the current month. Then use DATEADD to add 1 day, which gets the first day of the next month.
    Then just subtract months from that.

    select 
    DATEADD(month, -7, DATEADD(day, 1, EOMONTH(GETDATE()))) as date1, 
    DATEADD(month, -1, DATEADD(day, 1, EOMONTH(GETDATE()))) as date2
    
    date1 date2
    2021-07-01 2022-01-01

    Then the equivalent WHERE clause is this :

    WHERE COL1 >= DATEADD(month, -7, DATEADD(day, 1, EOMONTH(GETDATE())))
      AND COL1  < DATEADD(month, -1, DATEADD(day, 1, EOMONTH(GETDATE())))