How do I get three most recent months ? I have tried something as shown below just to get T -1, but it gives me wrong result:-
select to_date(CAST(MONTH(DATEADD(month, - 1, GETDATE())) as date),'YYYY/MM/DD')
Result:-
02/01/1841
You'd be better off asking the Intersystems Developer Community at https://community.intersystems.com/
However the following will return the first date of the current month and then the first and last dates of the previous 3 months. I'm no SQL expert so there might be less verbose ways of doing this!
select CAST(DATEADD('d' , 1-{fn DAYOFMONTH(CURRENT_DATE)}, CURRENT_DATE) AS DATE) as FirstDateThisMonth
,CAST(DATEADD('d', -{fn DAYOFMONTH(CURRENT_DATE)}, CURRENT_DATE) AS DATE) as LastDateLastMonth
,CAST(DATEADD('m', -1,DATEADD('d' , 1-{fn DAYOFMONTH(CURRENT_DATE)}, CURRENT_DATE)) AS DATE) as FirstDateLastMonth
,CAST(DATEADD('d', -1,DATEADD('m',-1,DATEADD('d' , 1-{fn DAYOFMONTH(CURRENT_DATE)}, CURRENT_DATE))) AS DATE) as LastDateTwoMonthsAgo
,CAST(DATEADD('m', -2,DATEADD('d' , 1-{fn DAYOFMONTH(CURRENT_DATE)}, CURRENT_DATE)) AS DATE) as FirstDateTwoMonthsAgo
,CAST(DATEADD('d', -1,DATEADD('m',-2,DATEADD('d' , 1-{fn DAYOFMONTH(CURRENT_DATE)}, CURRENT_DATE))) AS DATE) as LastDateThreeMonthsAgo
,CAST(DATEADD('m', -3,DATEADD('d' , 1-{fn DAYOFMONTH(CURRENT_DATE)}, CURRENT_DATE)) AS DATE) as FirstDateThreeMonthsAgo