Article
Yuri Marx · Oct 13, 2022 2m read

Function to get start and end dates for a month

If you want to get the initial and last day of a month, I have a ClassMethod to you (version 2, fixed an error found by Eduard):

ClassMethod GetMonthPeriods(InitialYear As %Integer, EndYear As %Integer) As %Status
{
    set sc = $$$OK
    set result = []
    For CurrentYear = InitialYear:1:EndYear {
        For CurrentMonth = 1:1:12 {
            set item = {}
            If CurrentMonth < 10 {
                Set CurrentMonthStr = "0"_CurrentMonth
            } Else {
                Set CurrentMonthStr = ""_CurrentMonth
            }
            Set item.StartDate = CurrentYear_"-"_CurrentMonthStr_"-01T00:00:00"
            Set NextMonth = CurrentMonth + 1
            If NextMonth = 13 {
                Set item.EndDate = CurrentYear_"-12-31T23:59:59"
            } Else {
                Set NextDate = $SYSTEM.SQL.Functions.TODATE(CurrentYear_"-"_NextMonth_"-01","YYY-MM-DD")
                Set NextDate = $INCREMENT(NextDate,-1)
                set item.EndDate = $ZDate(NextDate,3)_"T23:59:59"
            }
            
            Do result.%Push(item)
        }
    }
    Write result.%ToJSON()

    Return sc
}

The $SYSTEM.SQL.Functions.TODATE convert a string to a date and the $INCREMENT allows you increment or decrement a day from a date. When I call this method ..GetMonthPeriods(2022,2022) I got this:

[
    {
        "StartDate": "2022-01-01T00:00:00",
        "EndDate": "2022-01-31T23:59:59"
    },
    {
        "StartDate": "2022-02-01T00:00:00",
        "EndDate": "2022-02-28T23:59:59"
    },
    {
        "StartDate": "2022-03-01T00:00:00",
        "EndDate": "2022-03-31T23:59:59"
    },
    {
        "StartDate": "2022-04-01T00:00:00",
        "EndDate": "2022-04-30T23:59:59"
    },
    {
        "StartDate": "2022-05-01T00:00:00",
        "EndDate": "2022-05-31T23:59:59"
    },
    {
        "StartDate": "2022-06-01T00:00:00",
        "EndDate": "2022-06-30T23:59:59"
    },
    {
        "StartDate": "2022-07-01T00:00:00",
        "EndDate": "2022-07-31T23:59:59"
    },
    {
        "StartDate": "2022-08-01T00:00:00",
        "EndDate": "2022-08-31T23:59:59"
    },
    {
        "StartDate": "2022-09-01T00:00:00",
        "EndDate": "2022-09-30T23:59:59"
    },
    {
        "StartDate": "2022-10-01T00:00:00",
        "EndDate": "2022-10-31T23:59:59"
    },
    {
        "StartDate": "2022-11-01T00:00:00",
        "EndDate": "2022-11-30T23:59:59"
    },
    {
        "StartDate": "2022-12-01T00:00:00",
        "EndDate": "2022-12-31T23:59:59"
    }
]
2
1 187
Discussion (4)1
Log in or sign up to continue

Not sure about the last one:

	{
		"StartDate": "2022-12-01T00:00:00",
		"EndDate": "2022-11-30T23:59:59"
	}

Anyways, I usually use this logic:

  • Get 1st day of month
  • DATEADD 1 Month
  • DATEADD -1 Day

Accounts for year breaks, leap months, everything.

Fixed! Last row returns right now