extract month and year in oracle

oracle to-char
Adarsh Joshi picture Adarsh Joshi · Jul 28, 2017 · Viewed 11.3k times · Source

Why does below query work successfully?

select to_char(sysdate,'MM-YYYY') from dual;

But the following queries give an invalid number error:

select to_char('28-JUL-17','MM-YYYY') from dual;
select  to_char('7/28/2017','MM-YYYY') from dual;

Though, below query gives you the same date format.

select sysdate from dual; -- 7/28/2017 11:29:01 AM

Answer

mehmet baran picture mehmet baran · Jul 28, 2017

TO_CHAR function accepts only date or number. Maybe you can try this

select to_char(to_date('28-JUL-17', 'DD-MON-YY'),'MM-YYYY') from dual;

Related questions

ORACLE convert number to string

Need some help in converting numbers: select to_char(a, '99D99') , to_char(a, '90D99') from ( select 50 a from dual union select 50.57 from dual union select 5.57 from dual union select 0.35 from dual union select 0.4 from dual …

Read More
Oracle - Number to varchar

I have a table containing a column of type Number create table tmp ( /*other fields*/ some_field Number ) and in a PL SQL script, I want to convert that field to a varchar. However, i don't know its length, so …

Read More
SQL to_char Currency Formatting

I am facing a to_char() currency formatting problem here. The below code is working for me: SELECT TO_CHAR(10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = ''.,'' NLS_CURRENCY = $') "Amount" FROM DUAL; which will provide me with …

Read More