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