oracle 如何获得一周的第一天，取决于 NLS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17723300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the first day of the week, depending on NLS
提问by Alpaslan
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..
我想获得一周的第一天的日期，但我希望它依赖于 NLS 参数。说，当我在美国运行它时，它应该给我星期天的日期，但在土耳其它应该给我星期一..
select trunc(to_date(sysdate,'dd-mm-yy'),'iw')from dual;
How can I make it dependant?
我怎样才能让它依赖？
回答by Alex Poole
According to the documentation, trunc(sysdate, 'IW')gives you:
根据文档，trunc(sysdate, 'IW')给你：
Same day of the week as the first day of the calendar week as defined by the ISO 8601 standard, which is Monday
一周中的同一天与 ISO 8601 标准定义的日历周的第一天相同，即星期一
As you've seen, that is clearly not NLS-dependent.
正如您所见，这显然不依赖于 NLS。
You might think using Wwould 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.
您可能认为使用W会为您提供非 ISO、NLS 相关的版本，但它做了一些不同的事情 - 一周中的同一天作为一个月的第一天。所以现在运行，不管你的设置如何，这都会给你星期一，因为 7 月 1 日是星期一。
So you need either D, DYor DAY- they all behave the same:
因此，您需要D,DY或DAY- 它们的行为都相同：
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'). sysdateis 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_FORMATdidn't match (roughly, there is quite a bit of leeway) the dd-mm-yyyou use explicitly:
顺便说一句，您的原始查询正在执行to_date(sysdate,'dd-mm-yy'). sysdate已经是约会了。您正在强制从该日期隐式转换为字符串，该字符串将使用您的NLS_DATE_FORMAT，然后使用dd-mm-yy. 这不仅毫无意义，如果您NLS_DATE_FORMAT不匹配（粗略地说，有相当多的余地）dd-mm-yy您明确使用，它就会中断：
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_FORMATis inherited from NLS_TERRITORYby default, so this is likely to be an issue if you're expecting to deal with multiple regions anyway.
...等。默认情况下您NLS_DATE_FORMAT是从继承而来NLS_TERRITORY的，所以如果您希望处理多个区域，这可能是一个问题。
回答by Pinar
When u use iso week, it is same for all territories. It returns always mondays.
当你使用 iso 周时，它对所有地区都是一样的。它总是在星期一返回。
Instead of use this;
而不是使用这个；
select sysdate your_date,
trunc(sysdate,'IW') iso_start_of_week,
to_char(sysdate,'D') your_territory_day,
trunc(sysdate)- to_char(sysdate,'D') + 1 this_is_what_u_want
from dual
