13

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

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
vijaya
  • 315
  • 1
  • 4
  • 15

6 Answers6

15

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;
William_Wilson
  • 1,244
  • 7
  • 16
13

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;
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Exactly the same solutions as mine, but two minutes quicker and a great explanation concerning date and format. Well done. – Thorsten Kettner Sep 07 '17 at 12:03
  • One note to complement MTO: extract returns a number, so no need to covert to string, just do (in case of where): extract(month from column) = 03. Same for year, it's very handfull when making reports or charts :) – Marco May 12 '22 at 21:52
6

You want a string representing month and year in the format [M]M-YYYY. Oracle's TO_CHAR only supports MM-YYYY, though, so you'd have to get rid of a leading zero if any.

Two solutions:

trim(leading '0' from to_char(checked_date, 'mm-yyyy'))

or

extract(month from checked_date) || '-' || extract(year from checked_date) from dual
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
2

To get 1-2008 format use the following format with trimming leading zeroes:

select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(CHECKED_DATE,'MM-YYYY'),'0') AS A from Doctor_Checkup;
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
-1

SELECT ID_NO, CHECKED_DATE FROM DOCTOR_CHECKUP EXTRACT(MONTH FROM CHECKED_DATE) IN (6) AND EXTRACT(YEAR FROM CHECKED_DATE) IN (2019);

6 MEANS THE MONTH AND 2019 IS THE YEAR

Peter Mayeku
  • 1
  • 3
-1
LTRIM(TO_CHAR(TO_DATE(<date_field>,'YYYYMMDD'),'YYYY-MM'),'09'))
harun ugur
  • 1,718
  • 18
  • 18