Oracle Sql get only month and year in date datatype

Question

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

Answer 1

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