如何使用Oracle获取DATE的年份和月份

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错误

3 个解决方案

#1

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.

我在11g的sql中尝试了这个,它也适用于WHERE子句。

http://sqlfiddle.com/#!4/fb2b09/2

#2

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.

祝你好运。

#3

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';

#1