how to convert date into month number?
68,265
Solution 1
select
to_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
connect by level <= 12
)
select extract(month from dte) month, dte from fullYear ;
gives you
MONTH DTE
1 January, 30 2012 00:00:00+0000
2 February, 28 2012 00:00:00+0000
3 March, 28 2012 00:00:00+0000
4 April, 26 2012 00:00:00+0000
5 May, 25 2012 00:00:00+0000
6 June, 23 2012 00:00:00+0000
7 July, 22 2012 00:00:00+0000
8 August, 20 2012 00:00:00+0000
9 September, 18 2012 00:00:00+0000
10 October, 17 2012 00:00:00+0000
11 November, 15 2012 00:00:00+0000
12 December, 14 2012 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')
.
Author by
Taoqir
Updated on July 05, 2022Comments
-
Taoqir almost 2 years
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 number such as
Month 1 2
etc.