Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle date format

Saipraneeth IruguOct 31 2012 — edited Nov 1 2012
Hi,

I am trying to get the month number from this date format '19-OCT-12' . when i try to run the below statement i am getting an error like "not a valid month"

select to_date('19-OCT-12','MM') from dual

i need to get value as 10 can you please help me.

Regards
This post has been answered by Osama_Mustafa on Oct 31 2012
Jump to Answer

Comments

Osama_Mustafa
Answer
One More than way :
select to_char(to_date('17-OCT-67', 'dd-mon-rr'), 'MM')  from dual;
select extract (month from sysdate) "Month"
from dual;
Marked as Answer by Saipraneeth Irugu · Sep 27 2020
Saipraneeth Irugu
Thank you.
Osama_Mustafa
Pleasure to Help ;)
EdStevens
842265 wrote:
Hi,

I am trying to get the month number from this date format '19-OCT-12' . when i try to run the below statement i am getting an error like "not a valid month"

select to_date('19-OCT-12','MM') from dual

i need to get value as 10 can you please help me.

Regards
And to explain WHY the answer you got was correct .....

the to_date function returns a DATE data type. DATE is an internal datatype that has a consistent internal format. What you want to see is a character representation of part of a DATE. To get that you have to use the TO_CHAR function, supplying it a true DATE, and a format mask that describes how you want that date presented as a character string.

So, starting with 
'10-OCT-12'
you have a character string. I don't care how much you think it looks like a DATE, it is just a character string. To a computer, it is no different than 'Mary had a little lamb'.

supplying the to_date function 
to_date('10-OCT-12','dd-MON-yy')
Returns a DATE, an internally formatted value. What catches a lot of people is this: 
SQL> select to_date('10-OCT-12','dd-MON-yy') from dual;

TO_DATE('
---------
10-OCT-12

SQL>
And they see that date in that format and think that is the format of the DATE data element. But what's happening is oracle is turning your string into a date as specified by the to_date, but because it is being asked to display that, he has to do an to_char conversion to get it back to a character string that can be displayed. Lacking a to_char supplied with the query it will do an implicit conversion using the operative value of NLS_DATE_FORMAT to decide how to present that date.

The above select was internally converted by oracle to be 
select to_char(to_date('10-OCT-12','dd-MON-yy'),'dd-MON-yy') from dual;
you can read more on this at http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Edited by: EdStevens on Nov 1, 2012 6:48 AM
1 person found this helpful
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 28 2012
Added on Oct 31 2012
#general-database-discussions
4 comments
187 views