How to get the first day of the week, depending on NLS

Question

I want to get the date of the first day of the week but I want it to be dependant to NLS parameters. Say , when I run it on America it should give me Sundays date, but in Turkey it should give me Monday..

select trunc(to_date(sysdate,'dd-mm-yy'),'iw')from dual;

How can I make it dependant?

Solution

According to the documentation, trunc(sysdate, 'IW') gives you:

Same day of the week as the first day of the calendar week as defined by the ISO 8601 standard, which is Monday

As you've seen, that is clearly not NLS-dependent.

You might think using W would give you the non-ISO, NLS-dependent, version, but it does something different - the same day of the week as the first day of the month. So run now, that will give you Monday, regardless of your settings, since July 1st was a Monday.

So you need either D, DY or DAY - they all behave the same:

alter session set nls_territory = 'AMERICA';

select trunc(sysdate, 'D') from dual;

TRUNC(SYS
---------
14-JUL-13

alter session set nls_territory = 'TURKEY';

select trunc(sysdate, 'D') from dual;

TRUNC(SYSD
----------
15/07/2013

---

Incidentally, your original query is doing to_date(sysdate,'dd-mm-yy'). sysdate is already a date. You're forcing an implcit conversion from that date to a string, which will use your NLS_DATE_FORMAT, and then an explicit conversion back to a date using dd-mm-yy. Not only is that pointless, it would break if your NLS_DATE_FORMAT didn't match (roughly, there is quite a bit of leeway) the dd-mm-yy you use explicitly:

alter session set nls_date_format = 'dd/mm/yyyy';

select to_date(sysdate,'dd-mm-yy') from dual;

TO_DATE(SY
----------
18/07/2013

alter session set nls_date_format = 'dd-mon-rr';

select to_date(sysdate,'dd-mm-yy') from dual;

TO_DATE(S
---------
18-jul-13

alter session set nls_date_format = 'mm/dd/yyyy';

select to_date(sysdate,'dd-mm-yy') from dual;
select to_date(sysdate,'dd-mm-yy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

alter session set nls_date_format = 'yyyy-mm-dd';

select to_date(sysdate,'dd-mm-yy') from dual;
select to_date(sysdate,'dd-mm-yy') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string

... etc. And your NLS_DATE_FORMAT is inherited from NLS_TERRITORY by default, so this is likely to be an issue if you're expecting to deal with multiple regions anyway.