Login/Register
how to convert date into month number?
Asked Answered
Solved sqloraclesqldatetime
M

7

6

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.

Metopic answered 26/12, 2012 at 13:22 Comment(2)
what is datatype of column month? – Bon
go for datepart(month,getdate()) – Bon
L
15
select 
to_char(to_date('01-JAN-12','dd-mon-yy'),'mm') from dual;
Lucielucien answered 26/12, 2012 at 14:11 Comment(1)
Keep in mind: TO_CHAR() returns a string, not a number. A key consideration because '01' != '1' – Duvalier
M
15

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
Marlinmarline answered 26/12, 2012 at 13:41 Comment(2)
Doesn't actually address the OP's problem, which is to get the month as a number rather than a name. – Thevenot
@APC: huh? Extract(month from dte) returns the ##, the "Dte" column shown above is just the date returned from the with clause. I added spaces to help that be more apparent, but extract returns a number... – Marlinmarline
L
15
select 
to_char(to_date('01-JAN-12','dd-mon-yy'),'mm') from dual;
Lucielucien answered 26/12, 2012 at 14:11 Comment(1)
Keep in mind: TO_CHAR() returns a string, not a number. A key consideration because '01' != '1' – Duvalier
W
2

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').

Wives answered 26/12, 2012 at 13:26 Comment(1)
@NicholasKrasnov Thank you: I did not know this format trick. – Wives
F
1

datepart function do this easily

SELECT DATEPART(m, getdate())
Forestaysail answered 19/6, 2017 at 4:48 Comment(1)
The post is clearly tagged ORACLE. This solution is not ORACLE. – Safar
S
0

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

SELECT EXTRACT(MONTH FROM DATE '2012-03-15') FROM DUAL;

SELECT EXTRACT(MONTH FROM 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;
Steamer answered 26/12, 2012 at 20:7 Comment(0)
A
-1

You can use the "DatePart" function too :

http://docs.oracle.com/cd/E10530_01/doc/epm.931/html_esb_techref/maxl/dml/funcs/datepart.htm

Ashlan answered 26/12, 2012 at 13:30 Comment(1)
That's a link to the Hyperion documentation, which doesn't apply to the regular Oracle RDBMS. – Thevenot
N
-7
        SELECT  ( CASE yourDate
        WHEN 'Jan' THEN 1
        WHEN 'Feb' THEN 2
        WHEN 'Mar' THEN 3
        WHEN 'Apr' THEN 4
        WHEN 'May' THEN 5
        WHEN 'Jun' THEN 6
        WHEN 'Jul' THEN 7
        WHEN 'Aug' THEN 8
        WHEN 'Sep' THEN 9
        WHEN 'Oct' THEN 10
        WHEN 'Nov' THEN 11
        WHEN 'Dec' THEN 12
       END )
Nocturnal answered 26/12, 2012 at 13:27 Comment(1)
Why write a horrible CASE statement when Oracle's built-in functionality can do this? – Thevenot

© 2022 - 2024 — McMap. All rights reserved.