[oracle11g] Oracle Sql get only month and year in date datatype

I want to store only the month and the year in oracle data type.

I have a date like '01-FEB-2010' stored in a column called time_period.

To get only the month and year i wrote a query like

select to_char(time_period,'MON-YYYY') from fact_table;

I go the result as 'FEB-2010' which is fine but the only problem is that it is in varchar datatype.

So I did like

select to_date(to_char(time_period,'MON-YYYY'),'MON-YYYY') from fact_table

and I get 01-FEB-2010. Is it not possible to store only FEB-2010 in the date datatype

Easiest solution is to create the column using the correct data type: DATE

For example:

1. Create table:

   create table test_date (mydate date);

2. Insert row:

   insert into test_date values (to_date('01-01-2011','dd-mm-yyyy'));

To get the month and year, do as follows:

select to_char(mydate, 'MM-YYYY') from test_date;

Your result will be as follows: 01-2011

Another cool function to use is "EXTRACT"

select extract(year from mydate) from test_date;

This will return: 2011

SELECT to_char(to_date(month,'yyyy-mm'),'Mon yyyy'), nos
FROM (SELECT to_char(credit_date,'yyyy-mm') MONTH,count(*) nos
      FROM HCN
      WHERE   TRUNC(CREDIT_dATE) BEtween '01-jul-2014' AND '30-JUN-2015'
      AND CATEGORYCODECFR=22
      --AND CREDIT_NOTE_NO IS NOT  NULL
      AND CANCELDATE IS NULL
GROUP BY to_char(credit_date,'yyyy-mm')
ORDER BY to_char(credit_date,'yyyy-mm') ) mm

Output:

Jul 2014        49
Aug 2014        35
Sep 2014        57
Oct 2014        50
Nov 2014        45
Dec 2014        88
Jan 2015       131
Feb 2015       112
Mar 2015        76
Apr 2015        45
May 2015        49
Jun 2015        40