Monday, October 21, 2013

Extract Month/Year from dates in Oracle

This is how to extract month or year from Oracle (placing it here so I don't forget)

By Month -
SELECT EXTRACT(month FROM order_date) "Month",
  COUNT(order_date) "No. of Orders"
  FROM orders
  GROUP BY EXTRACT(month FROM order_date)
  ORDER BY "No. of Orders" DESC;


By Year -
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;


For more info, look at this page:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm


See PDF for more SQL References:
http://docs.oracle.com/cd/B19306_01/server.102/b14200.pdf