How To Convert Date Into Month Number?

I have a column Month in my table. The month name and date are stored in this month column like Month 01-JAN-12 02-FEB-12 and so on. How do I convert the DATE into month n

Solution 1:

selectto_char(to_date('01-JAN-12','dd-mon-yy'),'mm') from dual;

Solution 2:

Extract works perfectly for this

EXTRACT extracts and returns the value of a specified datetime field

with fullYear as(
  select (to_date('01-jan-12') +29*level) dte
    from dual
  connectby level <=12
  )

selectextract(monthfrom dte) month, dte from fullYear ;

gives you

MONTHDTE1January,302012 00:00:00+00002February,282012 00:00:00+00003March,282012 00:00:00+00004April,262012 00:00:00+00005May,252012 00:00:00+00006June,232012 00:00:00+00007July,222012 00:00:00+00008August,202012 00:00:00+00009September,182012 00:00:00+000010October,172012 00:00:00+000011November,152012 00:00:00+000012December,142012 00:00:00+0000

Solution 3:

Use TO_CHAR function as in TO_CHAR(my_column, 'MM').

For your specific format you would need the format converter TO_CHAR(month, 'FmMM') (thanks to Nicholas Krasnov for this trick).

If your month column is not already of a date type you will first need to convert it to a date: TO_CHAR(TO_DATE(month, 'DD-Mon-IY'), 'FmMM').

Solution 4:

Totally agree regarding writing a case when Oracle offers built-in functionality:

SELECTEXTRACT(MONTHFROMDATE'2012-03-15') FROM DUAL;

SELECTEXTRACT(MONTHFROM TO_DATE('01-JAN-12', 'DD-MON-RR')) month_to_number FROM DUAL;

select to_number(to_char(to_date('01-JAN-12','dd-mon-yy'),'mm')) month_to_number from     dual;

select to_number(to_char(trunc(sysdate), 'mm')) month_to_number from dual;

Solution 5:

datepart function do this easily

SELECT DATEPART(m, getdate())