有趣的日期问题：Oracle

[MountLion]

在Oracle中，select to_char(vdate,'ww') from dual可以求出vdate所在的周是本年度的第几周，现在的问题是，我要求第X周头一天是哪天，怎么做呢？

[flowerofwind]

这个啊,你做一个加法就行了啊,一年的第一天加上X*7,然后把那天取出来

[icevi]

应该可以知道这一天是周几吧，倒减一下就可以了

[KingSunSha]

下面是我写的满足你需要的一个函数,你可以取任意一周中的任意一天:

/*===========================================================================
Description: To derive the date of the given year week and day of week.
Assumption: First day of the week = monday.
Eg 199627, 'MON' --> 01-JUL-1996 (Monday of Week 27 in 1996)
Input : p_year_week - Input year week in YYYYWW format
p_day_of_week - 'MONDAY' - 'SUNDAY'.
Returns : date of the given year week and day of week in DATE format.
===========================================================================
*/
FUNCTION yw_to_date(p_year_week NUMBER,
p_day_of_week VARCHAR2) RETURN DATE IS
w_year NUMBER(4);
w_week NUMBER(2);
w_date DATE;
BEGIN
IF p_year_week IS NULL THEN
RETURN null;
END IF;
w_year := substr(p_year_week,1,4);
w_week := to_number(substr(p_year_week,5,2));
-- Get first MONDAY of week 1
SELECT next_day(to_date('28-DEC-'||to_char(w_year-1),'DD-MON-YYYY'),'MONDAY')
INTO w_date
FROM dual;
w_date := w_date + (w_week-1)*7;
IF upper(p_day_of_week) <> 'MONDAY' THEN
w_date := next_day(w_date,p_day_of_week);
END IF;
RETURN w_date;
END yw_to_date;

[luoluo_lm]

保留！

[MountLion]

好像给不了分了哟

[zenghongmei1]

呵呵