由于公司业务需要,IW,WW等等标准满足不了(IW以周一为第一天,WW以1月1号为第一天),因此自己写了个函数转换。
create or replace function fun_getWeek(Time varchar2) return number is
Week number(10);
begin
/* 获取当前时间是第几周,周天为一个星期的第一天。*/
select week into Week from(
select LEVEL as week,
TRUNC(TO_DATE(Time, 'YYYY-MM-DD'), 'YYYY') -
TO_CHAR(TRUNC(TO_DATE(Time, 'YYYY-MM-DD'), 'YYYY'), 'D') + 1 +
(LEVEL - 1) * 7 AS StarTtime,
TRUNC(TO_DATE(Time, 'YYYY-MM-DD'), 'YYYY') -
TO_CHAR(TRUNC(TO_DATE(Time, 'YYYY-MM-DD'), 'YYYY'), 'D') + 1 +
(LEVEL - 1) * 7 + 6 AS EndTime
from dual
connect by LEVEL <= 53) a
where to_date(Time,'YYYY-MM-DD') between a.startDate and a.endDate;
return Week;
EXCEPTION
WHEN others THEN
return 0;
end;
使用例子:
select fun_getWeek(to_char(DateTime,'yyyy-mm-dd')) as Week from dual;