User Locales and the Start of the Week

You've probably noticed that user records have a locale field in Salesforce, and chances are you've changed it at some stage to match your locale because you got sick of seeing dates in the wrong format. You might know that the locale also affects the default currency as well as time, date, and number formats. The documentation actually makes this quite clear:

Locales have you covered when it comes to formatting

What you might not know is that locales also change another setting, not displayed in this table, that specifies on what day the week starts because this varies widely around the world. I'm going to write that again just to make it obvious:

The Locale field defines what day is the start of the week.

This information is made available in Knowledge Article 000229511 but you'd have to know to search for an appropriate term to find it.

So What? I'm a Developer

Developers shouldn't have to worry about such things, the system should take care of it for you, right? Right. And it does, most of the time.

If you checkout the documentation for the various date literals in SOQL you'll see information such as this:

Date Literal	Range	Example
LAST_WEEK	Starts 12:00:00 on the first day of the week before the most recent first day of the week and continues for seven full days. Your locale determines the first day of the week.	SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK

The key part in this article of course, being this:

Your locale determines the first day of the week.

Happy days. So what about Apex? Well you're covered there too, for instance, the documentation for date methods specifically mentions locales and that they're used in methods such as toStartOfWeek():

toStartOfWeek() Returns the start of the week for the Date that called the method, depending on the context user's locale.

So It's All Good?

No. This is the point in the story is where we meet a small, innocent SOQL function called DAY_IN_WEEK; although when you call you will find that this function isn't as innocent as it first appears.

Unlike everything else that is related to the concept of weeks, this function does not respect the user's locale. You can use it in conjunction with convertTimeZone() if you don't want datetime fields returned as UTC, but that does not address the differences in locales. That this function doesn't respect the user's locale is not clearly stated, but the fact that the word 'locale' doesn't appear anywhere within the SOQL Date Functions documentation is telling. The documentation for the function itself looks like this:

Date Literal	Range	Examples
DAY_IN_WEEK()	Returns a number representing the day of the week for a date field.	1 for Sunday 7 for Saturday

Those example values? They are exactly what you get. Regardless of your locale, and regardless of what THIS_WEEK uses for the start of the week, and regardless of what the Apex method toStartOfWeek() uses, this always returns 1 for Sunday through to 7 for Saturday.

It took several hours of my time today to understand why a front-end chart that pulled data via remoting would show the start of the week as Sunday with data in the correct columns for me, but would start with Monday with data in the wrong columns in a client's org. As it turns out, my user in my org had the Australian locale, and since the client is based in London, the user naturally had the UK locale specified.

In the backend I was using the result of DAY_IN_WEEK to index into a list of objects representing days for this chart, and because it doesn't respect the locale that meant it was behaving incorrectly for the UK user. I'd likely have picked this up earlier if I was developing in the UK, but the Salesforce Australian locale defines the week as starting on Sunday, so rather inconveniently I never got an inkling of this lurking bug.

So Just Avoid Using It?

Maybe. If you can and it makes sense for you then do so. In my code I was dealing with dynamic SOQL and an unknown (i.e. user defined) field: it could be either a date or a datetime which is one of the reasons I used DAY_IN_WEEK in the first place. I needed records grouped by the date in question, and you can't group by datetime. You can convert datetime fields to dates with DAY_ONLY() but that can't be called on dates. Frankly rather than work through it all doing describes and overcomplicating the query-building logic it seemed easier to just correct the returned value.

private static Integer getDayOfWeekInLocale(Integer soqlDayInWeek)
{
 // Fun tip: The 1st of January, 1900 was a Monday.
 Date sunday = Date.newInstance(1899, 12, 31);

 // Number of days between the start of the week and Sunday, 0 means
 // sunday is the first day of the week, 1 means saturday etc. according
 // to the user's locale
 Integer sundayOffset = sunday.toStartOfWeek().daysBetween(sunday);

 // soql DAY_IN_WEEK returns 1-7, so -1 to get the 0-6 range, add the
 // offset for the locale and then mod 7
 return Math.mod((soqlDayInWeek - 1) + sundayOffset, 7);
}

To that end I wrote the above method, which works out the number of days between a given Sunday and what the start of the week containing that Sunday is according to the current user's locale. It then modifies the value passed in (assuming a range of 1-7) accordingly. Feel free to use it, but you might be better off just steering a path around DAY_IN_WEEK altogether. Either way, I hope this post saves some time and headaches.