---
title: "获得日期的年周次并且反算回日期"
author: "winzenghua"
date: "2009-06-04T17:34:00Z"
tags: []
slug: "untitled"
draft: false
lastmod: "2009-06-04T17:34:00Z"
id: 987881
url: "https://java4gis.iteye.com/blog/987881"
---

获得日期的周次  
SELECT TO\_CHAR(TO\_DATE('2007-05-21','YYYY-MM-DD'),'YYYYWW') FROM DUAL;  
SELECT TO\_CHAR(TO\_DATE('2007-05-27','YYYY-MM-DD'),'YYYYWW') FROM DUAL;  
结果：  
200721

反转计算：  
select to\_date(substr('200721',0,4)||'0101','YYYYMMDD')+to\_number(substr('200721',5,2))\*7-1 as weekfirstdate from dual;  
select to\_date(substr('200721',0,4)||'0101','YYYYMMDD')+to\_number(substr('200721',5,2))\*7-1 as weekfirstdate from dual;

开发中需要用到一个查询周的第一天日期的函数 搜索N久很难找到解决的方法 只要自己写一个先用着 代码如下 a\_week格式为 'YYYYIW' 如 '200801'表示2008年的第一周  
周使用IW方式计算 如果一年当中第52周别之后至当年的12月31日之间，还有大于或等于4天的话，则定为当年的第53周，否则剩余这些天数被归为下一年的第1周

01. CREATE OR REPLACE FUNCTION f\_week\_to\_date(a\_weekvarchar2) RETURN CHAR IS
02. v\_first\_datechar (10);
03. v\_date\_of\_weeknumber(1);
04. BEGIN
05. select to\_char(to\_date(substr(a\_week,1,4)|| '0101' , 'yyyymmdd' ), 'D' )
06. into v\_date\_of\_week
07. from dual;
08. v\_date\_of\_week:=v\_date\_of\_week-1;
09. ifv\_date\_of\_week&lt;=4then
10. select TO\_CHAR(TO\_DATE(SUBSTR(a\_week,1,4)|| '0101' , 'yyyymmdd' )+
11. SUBSTR(a\_week,5,2)\*7-7-v\_date\_of\_week+1,
12. 'yyyy-mm-dd' )
13. into v\_first\_date
14. from dual;
15. else
16. select TO\_CHAR(TO\_DATE(SUBSTR(a\_week,1,4)|| '0101' , 'yyyymmdd' )+
17. SUBSTR(a\_week,5,2)\*7-v\_date\_of\_week+1,
18. 'yyyy-mm-dd' )
19. into v\_first\_date
20. from dual;
21. end if;
22. return v\_first\_date;
23. END ;