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?

CodePudding user response：

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

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())))