Skip to content

Function Friday – More Date and Time Fun: startOf and dayOf

Photo by Tima Miroshnichenko from Pexels

Previously I’ve walked through how to do some date math, formatting, and parsing. Now I’ll drill into a few of the other date and time functions around getting certain values related to a particular date and time.

dayOf Functions

There are three functions that are closely related: dayOfMonth, dayOfWeek, dayOfYear. All three function in the same manner. They return a numerical value that represents the particular DateTime’s value in reference to the current month, week, or year.

For example, today is Friday, September 2, 2022. It is the 2nd day of the month, the 6th day of the current week, and the 245th day of the year.

All three functions have the same format:

dayOfMonth('<DATETIME>')
dayOfWeek('<DATETIME>')
dayOfYear('<DATETIME>')

They all expect the default date format for the DATETIME value. All three functions return an integer value.

dayOfMonth('2022-09-02T13:00:00')  // returns 2
dayOfWeek('2022-09-02T13:00:00')  // returns 5
dayOfYear('2022-09-02T13:00:00')  // returns 245

You might notice a little gotcha there. I mentioned that today is the 6th day of the week. For weeks, the value is 0 based and starts on Sunday. So, Sunday = 0, Monday = 1, and so forth. For month and year, the values are 1 based. Thus, 2 for day of the month and 245 for day of the year. That can throw off a lot of people until you get used to it.

startOf Functions

There are another three functions that are closely related: startOfDay, startOfHour, startOfMonth. These three functions are useful if you need to get a “base” starting DateTime value for another DateTime value. For example, with startOfHour, you would get the DateTime value rounded off to the top of the current hour, startOfDay would round off to the start of the day (i.e. midnight), and startOfMonth would round off to the first day of the month at midnight. Oddly, there isn’t a “startOfYear” function. It seems like that would be just as useful as the other three.

All three functions follow the same pattern:

startOfHour('<DATETIME>', '<FORMAT>')
startOfDay('<DATETIME>', '<FORMAT>')
startOfMonth('<DATETIME>', '<FORMAT>')

The FORMAT parameter is optional and represents the output format, not the input format. The input format must be in the default ISO 8601 format. If you do not specify the output format, then the output will be in the default format.

Examples:

startOfHour('2022-09-02T13:43:22')  // returns '2022-09-02T13:00:00.0000000'
startOfDay('2022-09-02T13:43:22')  // returns '2022-09-02T00:00:00.0000000'
startOfMonth('2022-09-02T13:43:22')  // returns '2022-09-01T00:00:00.0000000'
startOfDay('2022-09-02T13:43:22', 'yyyy-MM-dd')  // returns '2022-09-02'

As you can see, this function is quite useful for rounding off DateTime values, which are often used for things like report parameters.