CODEWITHSUNDEEP
sqloracle-databasedateplsqlformat
devio
devio

Reputation: 1169

Oracle PL/SQL : to_date() format not considered

When I execute this in PL/SQL Developer :

SELECT to_date('29/03/17 14:05','DD/MM/RR HH24:MI')  FROM dual;

Here's what I get :

3/29/2017 2:05:00 PM

How is this possible ? I use HH24 but it seems like it's HH that's being used instead. The day and month are also not in the format I entered.

Upvotes: 0

Views: 1351

Answers (3)

Boneist
Boneist

Reputation: 23578

(N.B. This answer is more to give more clarity to the other answers but it's too long for a comment.)

Oracle stores DATEs (and TIMESTAMPs etc) in its own specific format. Us humans represent dates in a variety of different formats and we deal with strings. Even us humans can get confused over what a date string represents, given no context - e.g. 03/09/2017 - is that the 3rd of September, 2017 or the 9th of March 2017?

So, when you pass a date into Oracle, you need to convert it into Oracle's date format by passing a string in and telling Oracle what the date format of that string is. This can be done using to_date() or via the DATE literal (which is always in yyyy-mm-dd format).

Conversely, when you want to read something that's stored in Oracle's DATE datatype, you need to tell Oracle how you want it to be displayed, which you can do by using to_char() with the appropriate format mask.

If you fail to explicitly convert the string-to-a-date or date-to-a-string, then Oracle uses the format specified in the NLS_DATE_FORMAT to decide how to do the conversion.

In your case, you didn't specify how you wanted your date to be displayed, so Oracle has to use to_char() along with the format contained in your NLS_DATE_FORMAT in order to display the date as a string, and clearly that's different to the format you passed the date-string in as.

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

Ok, conceptual excercise coming up

Which of these dates represents the 1st January 2017?

01/01/2017
2017-01-01
01-JAN-2017

That's right, all of them. The date datatype is not a format, it stores the value of the date, not how it appears.

If using Oracle, adjust your NLS_DATE_FORMAT to match your expectation, but again, this is just how the system will display the date, not how it stores it.

Upvotes: 2

Mani Gudvardarson
Mani Gudvardarson

Reputation: 66

What you are doing with the to_date method is parsing the string into a date object. If you then want to output the date object as string with a different format you should use the to_char method.

Example:

SELECT to_char(
    to_date('29/03/17 14:05','DD/MM/RR HH24:MI'), 
    'DD/MM/RR HH24:MI'
) FROM dual;

Upvotes: 2

Related Questions