Can someone just tell me on which columns we can apply to_date() function.

User_J6B4J

Because I have a column called hire_dt in my table with 'DD-MM-YYYY' format. Now if i want to change format and when every i am running below query

select To_date(HIRE_DATE,'DDMMYYYY') as Result from Emp1719; I am getting an error like.

ORA-01843: not a valid month

01843. 00000 - "not a valid month"

*Cause:   

*Action:

Can someone help me on this please.

And I also want to know exact functionality of to_date, as i didnt find correct info on any site. and on what kind on columns we can apply to_date function.

InoL

... with 'DD-MM-YYYY' format

select To_date(HIRE_DATE,'DDMMYYYY') 

If you put these two together, it seems pretty obvious that your select statement should be:

select To_date(HIRE_DATE,'DD-MM-YYYY') 

 i didnt find correct info on any site

Really? What about the Oracle user guide?

TO_DATE (oracle.com)

TO_DATE converts char to a value of DATE data type.

TexasApexDeveloper

Simple answer, if its a date column, you CAN USE to_date.. If the data is NOT in the proper format then you will get that error..

Thank you!

Tony Miller

White Rock, NM

InoL

https://community.oracle.com/tech/developers/discussion/comment/16835608#Comment_16835608

Very true, a confusing question by the poster. A DATE column doesn't have a format, so this looks like a VARCHAR2 column:

I have a column called hire_dt in my table with 'DD-MM-YYYY' format.

TexasApexDeveloper

Poster COULD wrap his original column in to_char if it IS a date, to allow conversion to character and then do the formatting they want, depends on what is being done with results..

Thank you!

Tony Miller

White Rock, NM