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.
select
to_char(to_date('01-JAN-12','dd-mon-yy'),'mm') from dual;
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With