sql - How to extract month number from date in Oracle

Question

I have ID_BB_SECURITY column where the date value is stored in this column for example '20190801'.

I want to get month number from this field for example for August date i want to get 8.

I tried below query but it throws an error 'literal does not match':

select to_number(to_date(ID_BB_SECURITY),'mm') from BT_EXPORT

I am not sure if i have to ignore null values so as to avoid the error

Answer 1

If the value is a number or string then you can convert it to a date with an appropriate mask - which is what you are missing, and what is causing the error you are getting (as it's using your session's NLS_DATE_FORMAT setting, which apparently does not match the format of the data; but which you should not rely on anyway, as @MTO said in comments):

to_date(ID_BB_SECURITY, 'YYYYMMDD')

and then extract the month number from that:

select extract(month from to_date(ID_BB_SECURITY, 'YYYYMMDD')) from BT_EXPORT

Or you could just use a substring:

select to_number(substr(ID_BB_SECURITY, 5, 2)) from BT_EXPORT;

Those assume a fixed consistent format, which is always a risky assumption when using the wrong data type. Ans if it's a number they are doing an implicit conversion from number to string, which you could turn into an explicit conversion for greater clarity.

If it's already a date - as it should be, of course - then you don't need the conversion:

select extract(month from ID_BB_SECURITY) from BT_EXPORT