SQL 如何使用 Oracle 获取 DATE 的年和月
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44994375/
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
How to get Year and Month of a DATE using Oracle
提问by user2597012
How can I get the year and month of a date in the where clause using Oracle.
如何使用 Oracle 在 where 子句中获取日期的年和月。
I used to be working with SQL server and it was as simple as YEAR(FIELDNAME) and MONTH(FIELDNAME).
我曾经使用过 SQL 服务器，它就像 YEAR(FIELDNAME) 和 MONTH(FIELDNAME) 一样简单。
I have tried the following:
我尝试了以下方法：
SELECT *
FROM myschema.mytablename
WHERE EXTRACT(YEAR FROM myDATE) = 2017
however it gives ORA-30076 Error
但是它给出了 ORA-30076 错误
回答by JRG
Have you tried EXTRACT()?
你试过EXTRACT()吗？
SELECT EXTRACT(YEAR FROM DATE '2017-12-01') FROM DUAL;
2017
SELECT EXTRACT(MONTH FROM DATE '2017-12-01') FROM DUAL;
12
I tried this in sql fiddle with 11g and it works in WHERE clause too.
我在 sql fiddle with 11g 中试过这个，它也适用于 WHERE 子句。
http://sqlfiddle.com/#!4/fb2b09/2
http://sqlfiddle.com/#!4/fb2b09/2
回答by fg78nc
SELECT *
FROM myschema.mytablename
WHERE TO_CHAR(myDATE, 'YYYY') = '2017';
Explicitly convert year part of DATE into CHAR and compare it with literal. For year and month comparison:
将 DATE 的年份部分显式转换为 CHAR 并将其与文字进行比较。年月比较：
SELECT *
FROM myschema.mytablename
WHERE TO_CHAR(myDATE, 'YYYY') = '2017' AND TO_CHAR(myDate, 'MM') = '07';
回答by Bob Jarvis - Reinstate Monica
Your query should work, but a better alternative would be
您的查询应该有效，但更好的选择是
SELECT *
FROM YOUR_TABLE
WHERE MY_DATE BETWEEN TO_DATE('01-JAN-2017', 'DD-MON-YYYY')
AND TO_DATE('01-JAN-2018', 'DD-MON-YYYY') - INTERVAL '1' SECOND
Best of luck.
祝你好运。
