Search code examples
sqlintersystems-cache

Get 3 most recent months (T, T-1, and T-2) in InterSys Cache DB

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
Solution

  • 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