如何在Oracle中将周数转换为星期几 [英] How do I convert a Week Number to From date of the week in oracle

问题描述

假设我输入WeekNo:14，则查询应返回From Date:2016年4月4日，因为第14周从4月4日开始到4月10日开始

Suppose I enter WeekNo: 14 the query should return the From Date: April 4th 2016, since the week 14 starts from 4th April to 10th April

select to_date('14','iw') FROM dual;

推荐答案

类似的东西? (适用于当前年份)在那里丢弃了其他年份的数据

something like this ? (it work for current year) there discard data from another years

with dates as (select to_char(
                        to_date('1.01.'||extract(year from sysdate),'dd.mm.yyyy'  ) + level -1 
                        ,'IW') we,
                        to_date('1.01.'||extract(year from sysdate),'dd.mm.yyyy'  ) + level -1 da
                 from dual 
                connect by level <= 365 + 10 )
select * from (
select case 
         when  -- we = null if number of week in jan > 1,2,3,4....
               ((to_number(we) > 40 )
               and extract(year from sysdate) = extract(year from da)
               and extract(month from da) = '01') or
               -- we = null when current year < year of da
               (extract(year from sysdate) != extract(year from da))
                then
                  null 
            else we
      end we,
      da
  from dates
)  
where we = 14
  and rownum = 1

这篇关于如何在Oracle中将周数转换为星期几的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！