如何在SQL中提取周数 [英] How to extract week number in sql

问题描述

我有一个varchar2类型的transdate列，该列具有以下主菜

I have a transdate column of varchar2 type which has the following entrees

01/02/2012
01/03/2012

等

我使用to_date函数在另一列中将其转换为日期格式.这是我得到的格式.

I converted it in to date format in another column using to_date function. This is the format i got.

01-JAN-2012
03-APR-2012

当我尝试提取Weekno时，我得到了所有空值.

When I'm trying to extract the weekno, i'm getting all null values.

从表名中选择to_char(to_date(TRANSDATE)，'w')作为周号.

select to_char(to_date(TRANSDATE), 'w') as weekno from tablename.

null
null

如何以上述格式从日期获取星期几?

How to get weekno from date in the above format?

推荐答案

将您的varchar2日期转换为真正的date数据类型之后，然后使用所需的掩码转换回varchar2:

After converting your varchar2 date to a true date datatype, then convert back to varchar2 with the desired mask:

to_char(to_date('01/02/2012','MM/DD/YYYY'),'WW')

如果要在number数据类型中使用星期数，则可以将语句包装在to_number()中:

If you want the week number in a number datatype, you can wrap the statement in 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.

这篇关于如何在SQL中提取周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！