Get Month Name from Date in Oracle

Get month name from date in Oracle

select to_char(sysdate, 'Month') from dual

in your example will be:

select to_char(to_date('15-11-2010', 'DD-MM-YYYY'), 'Month') from dual

How to extract month number from date in Oracle

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

Get month number from month name

Check your oracle language, it seems to be in another.

Because im trying with this code:

select to_char(to_date('ABR 1990','MONTH yyyy'), 'mm') as MONTH from dual;

And i get:

Note: My native language is Spanish.

Get month name from date in Oracle

select to_char(sysdate, 'Month') from dual

in your example will be:

select to_char(to_date('15-11-2010', 'DD-MM-YYYY'), 'Month') from dual

Convert varchar to date in Oracle SQL with month name

Use

        TO_DATE(review_date, 'MONTH DD, 
        YYYY' )

       TO_DATE(complete_date, 
       'YYYYMMDD') 

This will read your data in the given month dd yyyy format and will convert to orackes default date format. You cannot actually modify the default date format using to_date() but can use to_char() to store modified date format as varchar

Extract month and year from date in oracle

If the field is already a date column, you can simply cast it to the format you want:

select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(CHECKED_DATE,'mm-yyyy'),'0') AS A from Doctor_Checkup;

If it is a text column, you will need to cast to a date with format first:

select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(TO_DATE(CHECKED_DATE,'dd/mm/yyyy'),'mm-yyyy'),'0') AS A from Doctor_Checkup;

---

Related Topics