Oracle Sql get only month and year in date datatype

user_226906 0
Viewed 230820

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

oracle11g
4 Answers

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).
user_116219910
Related