Extract month and year from date in oracle

Question

what is the query for extract month and year from full date. my data is like this: 1/29/2008 I tried this query:

select ID_NO, CHECKED_DATE, to_date(TO_CHAR(CHECKED_DATE, 'MON-YYYY'), 'MON-YYYY') AS A 
from Doctor_Checkup;

but It will give output: 1/1/2008

Expected output: 1-2008

Answer 1

A date does not have a format - it is stored internally to the database as 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date has a format.

One thing to note is that a date always has the year, month, day, hour, minute and second components. Doing:

to_date(TO_CHAR(CHECKED_DATE, 'MON-YYYY'), 'MON-YYYY')

Is effectively the same as doing:

TRUNC( Checked_Date, 'MM' )

and will still have a day, hour, minute and second component but will have been truncated to midnight of the first day of the month. The user interface may just be have its preferences set to not display the time component (but the date will still have one).

What you want to do is convert the date to a formatted string:

select ID_NO,
       CHECKED_DATE,
       TRIM( LEADING '0' FROM TO_CHAR( CHECKED_DATE, 'MM-YYYY') ) AS A 
from   Doctor_Checkup;

or

select ID_NO,
       CHECKED_DATE,
       EXTRACT( MONTH FROM CHECKED_DATE )
         || '-' || EXTRACT( YEAR FROM CHECKED_DATE ) AS A 
from   Doctor_Checkup;

Answer 2

If the field is already a date column, you can simply cast it to the format you want:

select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(CHECKED_DATE,'mm-yyyy'),'0') AS A from Doctor_Checkup;

If it is a text column, you will need to cast to a date with format first:

select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(TO_DATE(CHECKED_DATE,'dd/mm/yyyy'),'mm-yyyy'),'0') AS A from Doctor_Checkup;