WITH tt AS (SELECT to_date(extract(YEAR FROM SYSDATE) || '1225', 'yyyymmdd') christmas FROM dual) SELECT christmas FROM (SELECT rownum rn, christmas, diff FROM (SELECT christmas, abs(SYSDATE - christmas) diff FROM tt UNION SELECT (christmas - INTERVAL '1' YEAR), abs(SYSDATE - (christmas - INTERVAL '1' YEAR)) FROM tt UNION SELECT (christmas + INTERVAL '1' YEAR), abs(SYSDATE - (christmas + INTERVAL '1' YEAR)) FROM tt) ORDER BY diff) WHERE rn = 1;
select chrdate from ( select (to_char(sysdate,'yyyy')-level+1)||'1225' chrdate from dual connect by level<=2 order by abs(sysdate-to_date(chrdate,'yyyy-mm-dd')) ) where rownum =1;
chrdate结果为字符型 想要date类型的话 要进行to_date转换下
select case when abs(trunc(to_date(to_char(sysdate,'year')||'-12-25','yyyy-mm-dd')-sysdate))> (add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year'))/2 then add_months(to_date(to_char(sysdate,'year')||'-12-25','yyyy-mm-dd'),-12) else to_date(to_char(sysdate,'year')||'-12-25','yyyy-mm-dd') end from dual;
SELECT CASE WHEN TO_CHAR(SYSDATE,'mmdd')<='1225' THEN add_months(TRUNC(SYSDATE,'yy'),12)-7 ELSE add_months(TRUNC(SYSDATE,'yy'),24)-7 END chrs FROM dual;
(SELECT to_date(extract(YEAR FROM SYSDATE) || '1225', 'yyyymmdd') christmas FROM dual)
SELECT christmas
FROM (SELECT rownum rn, christmas, diff
FROM (SELECT christmas, abs(SYSDATE - christmas) diff
FROM tt UNION
SELECT (christmas - INTERVAL '1' YEAR),
abs(SYSDATE - (christmas - INTERVAL '1' YEAR))
FROM tt UNION
SELECT (christmas + INTERVAL '1' YEAR),
abs(SYSDATE - (christmas + INTERVAL '1' YEAR))
FROM tt)
ORDER BY diff)
WHERE rn = 1;
(
select (to_char(sysdate,'yyyy')-level+1)||'1225' chrdate from dual
connect by level<=2
order by abs(sysdate-to_date(chrdate,'yyyy-mm-dd'))
)
where rownum =1;
select case
when abs(trunc(to_date(to_char(sysdate,'year')||'-12-25','yyyy-mm-dd')-sysdate))>
(add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year'))/2
then add_months(to_date(to_char(sysdate,'year')||'-12-25','yyyy-mm-dd'),-12)
else to_date(to_char(sysdate,'year')||'-12-25','yyyy-mm-dd')
end
from dual;
CASE
WHEN TO_CHAR(SYSDATE,'mmdd')<='1225'
THEN add_months(TRUNC(SYSDATE,'yy'),12)-7
ELSE add_months(TRUNC(SYSDATE,'yy'),24)-7
END chrs
FROM dual;