CODEWITHSUNDEEP
sqloracle-databasedate
user2597012
user2597012

Reputation: 647

How to get Year and Month of a DATE using Oracle

How can I get the year and month of a date in the where clause using Oracle.

I used to be working with SQL server and it was as simple as YEAR(FIELDNAME) and MONTH(FIELDNAME).

I have tried the following:

SELECT * 
FROM myschema.mytablename
WHERE   EXTRACT(YEAR FROM  myDATE)  = 2017

however it gives ORA-30076 Error

Upvotes: 9

Views: 171134

Answers (4)

Marco Trejo
Marco Trejo

Reputation: 11

I did this with the function EXTRACT() and it works good for me.

I'm gonna share the query code here:

SELECT extract(YEAR from s.CREATE_DATE) as YEAR, extract(MONTH from s.CREATE_DATE) as MONTH, s.SALES_PERSON_GID, COUNT(s.SALES_ORDER_ID) as "TOTAL ORDERS" FROM SALES_ORDERS s, SALES_ORDER_STATUS ss WHERE s.SALES_ORDER_ID = ss.SALES_ORDER_ID and ss.STATUS_TYPE_ID = 'SALE ORDER STATUS' and ss.STATUS_VALUE_GID = 'SALE ORDER STATUS_DELIVERED' GROUP BY  s.SALES_PERSON_GID,s.CREATE_DATE

enter image description here

Upvotes: 1

fg78nc
fg78nc

Reputation: 5232

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:

SELECT * 
    FROM myschema.mytablename
    WHERE   TO_CHAR(myDATE, 'YYYY') = '2017' AND TO_CHAR(myDate, 'MM') = '07';

Upvotes: 12

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

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.

Upvotes: 1

JRG
JRG

Reputation: 4187

Have you tried 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.

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

enter image description here

Upvotes: 25

Related Questions