如何使用一周的非标准第一天在 Oracle 中计算一年中的一周？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4110943/
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 do I calulate week of year in Oracle using a non-standard first day of the week?
提问by dkackman
I have a query that needs to return the "week of year" of a date field but the customer of the query uses a non-standard first day of the week so TO_CHARwith 'IW'isn't returning the expected result. In this case the first day of the week is Saturday and Friday is the seventh day of the week.
我有一个查询，需要返回日期字段的“年度周刊”，但查询的客户使用一周的非标准第一天，所以TO_CHAR有'IW'没有返回预期的结果。在这种情况下，一周的第一天是周六，周五是一周的第七天。
With T-SQL I'd use DATEPARTand SET DATEFIRST.
使用 T-SQL 我会使用DATEPART和SET DATEFIRST。
What is the Oracle equivalent? The Oracle answers I've found in the google all talk about setting the NLS_TERRITORYlike so ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM';but I'm not seeing where I can pick an arbitrary day (other than perhaps finding a territory that uses Saturday).
什么是 Oracle 等价物？我在谷歌中找到的 Oracle 答案都在谈论设置NLS_TERRITORY类似的内容，ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM';但我没有看到我可以选择任意一天的位置（除了可能找到使用星期六的区域）。
回答by Craig
IW works with a Monday - Sunday week, so this should get you what you are looking for. Basically, get the week according to the day 2 days from now:
IW 适用于周一至周日，因此这应该可以满足您的需求。基本上，根据从现在起 2 天后的第 2 天获取一周：
to_char(your_date + 2, 'IW')
回答by Rajesh Chamarthi
Oracle's default week format calculates the week number from the first day of the year instead of the first day of the week.
Oracle 的默认周格式从一年的第一天而不是一周的第一天开始计算周数。
So if a year starts on 01-jan-2009 and the first day is on Wednesday, the week No. 1 will be from 01-jan-2009 to 08-jan-2009 (wednesday to tuesday).
因此，如果一年从 01-jan-2009 开始，第一天是星期三，那么第 1 周将从 01-jan-2009 到 08-jan-2009（星期三到星期二）。
You can use the "iw" format (and a little tweak) if you need the week range to start from sunday through saturday. http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10749/ch9sql.htm#CIHGFJEI
如果您需要从星期日到星期六开始的周范围，您可以使用“iw”格式（并稍作调整）。http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10749/ch9sql.htm#CIHGFJEI
Try this code below. I basically use the "IW" format and add a condition to get the week number to start from a given date.. say...01-jul-2008.
试试下面的这段代码。我基本上使用“IW”格式并添加一个条件来获取从给定日期开始的周数……比如说……2008 年 7 月 1 日。
select target_date,
to_char(target_date+1,'iw') week_sun_thru_saturday,
to_number(to_char(target_date+1,'iw')) -
to_number(to_char( to_date('10-jul-2008','dd-mon-yyyy')+1,'iw')) week_from_01_jul_2008
from t;
Remember..This code will not give week number 1 from jul1st to jul-07 .unless of course 01-jul-2008 is a sunday ;)
请记住..此代码不会给出从 jul1st 到 jul-07 的第 1 周。除非 01-jul-2008 是星期日；)
