SQL 在oracle中查找一个月中的周数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13155988/
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
Finding week number in a month in oracle
提问by Pravin Satav
I have table where I have stored all mondays e.g (1st oct, 8th oct, 15th oct, 22nd oct, 29th oct), I need to find out week of that month which will be like for 1st oct it will 1, 8th oct 2 and henceforth.. for Sept counter should start at 1 again. Can this is done through sql query, database is oracle 10g?
我有一张表，我存储了所有星期一，例如（1 月 1 日、10 月 8 日、15 日、10 月 22 日、10 月 29 日），我需要找出那个月的那一周，10 月 1 日是 1 月 8 日 2此后.. 9 月计数器应再次从 1 开始。这个可以通过sql查询完成吗，数据库是oracle 10g？
if first on month is coming on wednesday then first monday in that month should be marked as 1, its second week but need as 1.
如果第一个月在星期三到来，则该月的第一个星期一应标记为 1，第二周但需要标记为 1。
回答by Florin Ghita
select to_char(your_date,'W') from dual;
will do it.
会做的。
A nice table to analyse is here:
一个很好的分析表在这里：
Here is a SQL Fiddle to prove it.
回答by Srinivasan KR
A small change is required in the query, check for Jun 01 2014, it shows as first week = 2. Use the below query to check. Same case is applicable for Jan 01 2017
查询中需要一个小的更改，检查 2014 年 6 月 1 日，它显示为第一周 = 2。使用以下查询进行检查。同样的情况适用于 2017 年 1 月 1 日
select to_date('06/01/2014','mm/dd/yyyy') curr_day, next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-8, 'sun') prev_week, trunc( (to_date('06/01/2014','mm/dd/yyyy')-next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-8, 'sun'))/7 )+1 "week" from dual;
选择 to_date('06/01/2014','mm/dd/yyyy') curr_day, next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-8 , 'sun') prev_week, trunc( (to_date('06/01/2014','mm/dd/yyyy')-next_day(trunc(to_date('06/01/2014','mm/dd/yyyy') ),'mm')-8, 'sun'))/7 )+1 "week" from dual;
The correct query should be:
正确的查询应该是：
select to_date('06/01/2014','mm/dd/yyyy') curr_day, next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-7, 'sun') prev_week, trunc( (to_date('06/01/2014','mm/dd/yyyy')-next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-7, 'sun'))/7 )+1 "week" from dual;
选择 to_date('06/01/2014','mm/dd/yyyy') curr_day, next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-7 , 'sun') prev_week, trunc( (to_date('06/01/2014','mm/dd/yyyy')-next_day(trunc(to_date('06/01/2014','mm/dd/yyyy') ),'mm')-7, 'sun'))/7 )+1 "week" from dual;
回答by smily
The source http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7482139067917
来源http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7482139067917
select
x,
next_day(trunc(x,'mm')-8, 'sun'),
trunc( (x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1 "week"
from t;
Week started from Monday 'MON'
一周从星期一“MON”开始
there is you can check it SQL Fiddle
你可以检查它SQL Fiddle
