How to determine first day of the week based on the locale?

[cosmin_jimborean]

Hi All,

I have a requirement to determine the first day of the week based on the locale.

For example,

• in US, the first day of this week is Sunday, March 29
• in Germany, the first day of this week is Monday, March 30

I know I can get the first day of the week using function WEEKDAY, so I tried below SQL which works, but since WEEKDAY is hardcoded Monday(0) to Sunday(6), the results are always giving the first day of the week as Monday.

select	now() as "TODAY",
	weekday(now()) as "WEEKDAY",
	add_days(now(), -weekday(now())) as "WEEK_FIRST_DAY"

from dummy;

Any ideas how to make this locale dependent?

Thanks and best regards,

Cosmin

[lbreddemann]

Hi Cosmin,

the LOCALE as you wrote is ambiguous.

Which locale are you referring to?

• The end-user/front end consumer locale?
• The report-level setting (e.g. whatever the end-user's locale settings are, the report will show up in e.g. UK-English)?
• The data model language settings?
• The database user locale setting?

Depending on the answer to this question, you'd have to consider different approaches for your modeling here.

- Lars

[Former Member]

Hi Lars,

           My name is Krunal and I am working with Cosmin on this issue. Here LOCALE means end-user/SAPUI5 front end LOCALE. Our issue here is that we have front end in SAPUI5 and we are using new sap.ui.core.LocaleData to retrieve start day of the week and it works fine. We need to calculate same thing from HANA and return data based on this LOCALE setting, we tried many thing but we couldn't find any solution. Let me know if this is not clear for you. Thank you in advance.

Regards,

Krunal Patel

[lbreddemann]

There is no locale-dependent function on db/hana-core/sql/information-model level built-in to SAP HANA.

If you want something like that, you'll have to create your own function for this.

E.g. a user defined function (UDF) could take the wanted locale either from the CONTEXT information or as a parameter (or both, using the CONTEXT information as default/fall-back) and return the locale-dependent first day of a given week as a date.

More important than this implementation detail is that it seems like mixing up different system concerns here:

- business logic for the calculation

and

- front end/presentation level settings (including the LOCALE).

So, based on your definition of LOCALE, you want to see a change in business logic to happen (change of the first day of the week definition). I don't think that this is what you want.

Very often things like "first day of ..." are related to some sort of business calendar. Such a business calendar domain usually also covers things like "working days", "public holidays", "financial accounting period" and so on. All this doesn't depend on the front end settings, but have their definition based in the business (contracts, legal requirements...).

Rather than trying to push down presentation preferences to the business model, I recommend to have a separate function set/module/package that gives you the calendar functions.

- Lars

[henrique_pinto]

Such locale-based conversion are usually handled in the UI layer.

Given you're using SAPUI5, you're able to write JS code (either in HANA's XS Engine or any other SAP AS). If so, check this out:

javascript - Getting what is the first day of the week based on Locale with momentJs - Stack Overflo...

[cosmin_jimborean]

Thanks a lot guys for your answers.

Actually, the locale that I was referring to was the locale of the database user. I apologize for the confusion above.

Normally, I agree that such locale specific logic should be handled in the UI layer and the first day of the week should be passed as a parameter in the request to the backend.

But, the specific framework we use, in a particular circumstance, does not allow us to calculate and pass this to the HANA backend, it only allows static (hard-coded) parameters. Hence, if I want it to be dynamic, I'm forced to determine the week first and last days in the HANA calculation view based on some other input and then use these as my date range in the next queries.

I cannot use JS code in this particular case, I can only use SQL.

So far, my best option is to write a SQL function which:

1. Gets the locale from the context using

select session_context('LOCALE_SAP'),session_context('LOCALE') from DUMMY;

     2.  Based on the locale, determines and returns the first day of the week. But here, I’m stuck again to hardcode the mapping “locale -> first day of week” because I cannot find any source table from which to get this info. Any better solution for this?

I really appreciate your help.

Best,

Cosmin

[lbreddemann]

Hi Cosmin,

there is no general right or wrong about the first day of the week. That's why there's basically always the option to select which option should be used independent from the chosen locale.

In your position, I'd probably - as mentioned already - build a function that would return the first day of the week based on a parameter and only resort to the hard coded built-in logic based on the LOCALE (go for LOCALE as LOCALE_SAP really only maps to the one-character encoding used in SAP NetWeaver tables).

That way you can at least be a bit flexible.

- Lars

[Former Member]

Afaik,

there's no SET DATEFIRST  or similar in Hana, so you'll have to handle it yourself. I didn't find any system parameter for that also.

Maybe someone knowes better, or if it is planned to be in future SPS's

But depending on locale it shouldn't be too complicated.

Hope this helps.