-1

I am using Oracle SQL Developer and have a field where dates are stored like JAN-19, FEB-19, etc.. The number is the year. I need to get the Month Number for this field. So, the month number for JAN-19 would be 1, FEB-19 would be 2, etc..

How can I do this?

Jordan Davis
  • 855
  • 3
  • 15
  • 22
  • So you dates are not stored using a DATE datatype but as strings??? – APC Apr 11 '17 at 20:16
  • Yes, they are stored as strings. – Jordan Davis Apr 11 '17 at 20:17
  • Try reading this question: http://stackoverflow.com/questions/14041200/how-to-convert-date-into-month-number. Oracle SQL has a lot of really useful date functions and there are many different ways you could tackle this. The documentation is very helpful. – CPR Apr 11 '17 at 20:17
  • See the [Oracle Datetime Format Model documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00212). – MT0 Apr 12 '17 at 09:02

1 Answers1

1

" they are stored as strings"

Convert the strings to dates, then cast back to strings get the month number.

Select col_whatever -- the target column
        ,  to_char(to_date(col_whatever, 'MON-YY'), 'MM') as month_no
from your_table
/
APC
  • 144,005
  • 19
  • 170
  • 281