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 data开发者_StackOverflowtype.

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

"FEB-2010" is not a Date, so it would not make a lot of sense to store it in a date column.

You can always extract the string part you need , in your case "MON-YYYY" using the TO_CHAR logic you showed above.

If this is for a DIMENSION table in a Data warehouse environment and you want to include these as separate columns in the Dimension table (as Data attributes), you will need to store the month and Year in two different columns, with appropriate Datatypes...

Example..

Month varchar2(3) --Month code in Alpha..
Year  NUMBER      -- Year in number

or

Month number(2)    --Month Number in Year.
Year  NUMBER      -- Year in number

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

The recommended solution for this is to have a field / columns defined as DATE or DATETIME datatype. This is have a proper date formatted value, and to extract just Month-Year from date will be easy, and there are below two ways.

1. SELECT TO_CHAR(CREATE_DATE,'MON YYYY') FROM DUAL; This will return the date as "JAN 2022". Also we could change the forma if required.
2. Could also use "EXTRACT" keyword, but the issue is we have to use it twice to get above format, as EXTRACT will return one value at a time. So first retrieve MONTH and then YEAR (or vice versa).