CODEWITHSUNDEEP
sqloracle-database
andreww
andreww

Reputation: 223

converting yyyy-mm-dd into mm/dd/yyyy

I'm trying to convert YYYY-MM-DD HH24:MM:SS INTO MM/DD/YYYY HH24:MM:SS

The last try was : 

SQL> select to_date(to_date('2016-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS') from dual

I got error which says :Invalid monhts, I'm very confused and tired of that, could anyone take a look ?

Upvotes: 1

Views: 3928

Answers (2)

MT0
MT0

Reputation: 168671

Dates do not have a format - they are represented internally by 7- or 8- bytes. It is only when a client program is passed a date that that client program (potentially) gives it a format.

The default string format for dates in SQL/Plus or SQL Developer is set by the NLS_DATE_FORMAT session parameter. Other clients will typically have parameters that you can set for the default date format (if they don't also use the NLS settings). However, beware that the NLS_DATE_FORMAT is a session parameter so it belongs to the user's session and multiple users can each have a different value for the parameter corresponding to how they have set it.

If you want to give a date a specific format then you will need to convert it to a string:

SELECT TO_CHAR( DATE '2016-05-01', 'MM/DD/YYYY HH24:MI:SS' )
FROM   DUAL;

Why your query does not work:

TO_DATE( value, frmt ) expects a string value and a format mask. The inner TO_DATE('2016-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') is perfectly valid and will return the DATE '2016-05-01'.

However, the outer TO_DATE() is then being passed the DATE type you have just generated and the string format (instead of the two strings it is expecting). Oracle will implicitly call TO_CHAR() on the date and use the NLS_DATE_FORMAT session parameter as the format mask. This is generating a string from the date and, given the error, the value of NLS_DATE_FORMAT is not MM/DD/YYYY HH24:MI:SS so when the outer TO_DATE() tries to parse the implicitly created string it fails as the first number it reads is invalid for a month.

Upvotes: 4

Sud
Sud

Reputation: 153

This should work. Use to_char

select to_char(to_date('2016-05-01 00:00:00','YYYY-MM-DD 
HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS') from dual

Upvotes: 1

Related Questions