sql oracle case 日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29675859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql oracle case when date
提问by piguy
This is what I got so far:
这是我到目前为止得到的：
select
to_char(sysdate, 'yyyy') Time
from
dual;
Which gives me:
这给了我：
TIME
2015
Its working until this point.
它的工作直到这一点。
I would like to add
我想补充
- if the month is
>= 7I get as output01.07.current year - if the month is
<= 7I get as output01.07.(current year - 1 year)
- 如果月份是
>= 7我得到的输出01.07.current year - 如果月份是
<= 7我得到的输出01.07.(current year - 1 year)
Any ideas how to handle this? I thought about CASE WHEN but I dont get know how.
任何想法如何处理这个？我想过 CASE WHEN 但我不知道如何。
Thanks!
谢谢！
回答by Lalit Kumar B
A simple CASEexpression would do the job.
一个简单的CASE表达式就可以完成这项工作。
For example,
例如，
SQL> SELECT
2 '01.07.' ||
3 CASE
4 WHEN TO_CHAR(SYSDATE, 'MM') < '07'
5 THEN
6 TO_CHAR(SYSDATE, 'YYYY')
7 ELSE
8 TO_CHAR(add_months(SYSDATE,-12), 'YYYY')
9 END case_date
10 FROM dual;
CASE_DATE
----------
01.07.2015
SQL>
To keep it even more precise, you could keep the common value outside the CASE expression:
为了使其更加精确，您可以将公共值保留在 CASE 表达式之外：
SQL> SELECT '01.07.'
2 ||
3 CASE
4 WHEN TO_CHAR(SYSDATE, 'MM') < '07'
5 THEN TO_CHAR(SYSDATE, 'YYYY')
6 ELSE TO_CHAR(add_months(SYSDATE,-12), 'YYYY')
7 END case_date
8 FROM dual;
CASE_DATE
----------
01.07.2015
SQL>
回答by Ilia Maskov
Using extract more readable
使用提取更具可读性
SELECT
to_date((CASE
WHEN extract(MONTH FROM SYSDATE) >= 7 THEN
0
ELSE
-1
END) + extract(YEAR FROM SYSDATE) || '07-01', 'yyyy-mm-dd') END
FROM dual
