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
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子句。
#2
0
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
0
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
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子句。
#2
0
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
0
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';