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

User · Answer

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

For example:

Create table:

create table test_date (mydate date);
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

User · Answer

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

User · Answer

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

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

Examples related to oracle11g