SQL 如何在sql中提取周数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16530044/
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 extract week number in sql
提问by user2133404
I have a transdate column of varchar2 type which has the following entrees
我有一个 varchar2 类型的 transdate 列，它具有以下主菜
01/02/2012
01/03/2012
etc.
等等。
I converted it in to date format in another column using to_date function. This is the format i got.
我使用 to_date 函数将其转换为另一列中的日期格式。这是我得到的格式。
01-JAN-2012
03-APR-2012
When I'm trying to extract the weekno, i'm getting all null values.
当我试图提取weekno时，我得到了所有空值。
select to_char(to_date(TRANSDATE), 'w') as weekno from tablename.
从表名中选择 to_char(to_date(TRANSDATE), 'w') 作为 weekno。
null
null
How to get weekno from date in the above format?
如何以上述格式从日期获取weekno？
回答by Wolf
After converting your varchar2date to a true datedatatype, then convert back to varchar2with the desired mask:
将您的varchar2日期转换为真正的date数据类型后，然后varchar2使用所需的掩码转换回：
to_char(to_date('01/02/2012','MM/DD/YYYY'),'WW')
If you want the week number in a numberdatatype, you can wrap the statement in to_number():
如果您想要number数据类型中的周数，您可以将语句包装在to_number()：
to_number(to_char(to_date('01/02/2012','MM/DD/YYYY'),'WW'))
However, you have several week number options to consider:
但是，您有几个周数选项需要考虑：
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
回答by Vinicius Lima
Try to replace 'w' for 'iw'. For example:
尝试将 'w' 替换为 'iw'。例如：
SELECT to_char(to_date(TRANSDATE, 'dd-mm-yyyy'), 'iw') as weeknumber from YOUR_TABLE;
回答by Sheen
Select last_name, round (sysdate-hire_date)/7,0) as tuner
from employees
Where department_id = 90
order by last_name;
回答by miz
Use 'dd-mon-yyyy'if you are using the 2nd date format specified in your answer. Ex:
使用'dd-mon-yyyy'如果您使用的是在你的答案中指定的第二个日期格式。前任：
to_date(<column name>,'dd-mon-yyyy')
