Converting string into date does not return the date in specific format

Question

I'm working on Oracle. As you can understand from the title, the problem is in this code:

select TO_DATE( '21991231', 'YYYYMMDD' ) from dual;

And the result is:

31/12/2199

I want to achieve this result:

21991231

Why is Oracle doing this? How can escape this problem?

@Asjon, that's not what [`to_date`](http://docs.oracle.com/database/121/SQLRF/functions219.htm) does. — William Robertson, May 08 '17 at 10:27
Why are you converting it at all if it's already in the format you want? You would have to convert your string to a date (which has no intrinsic format!) and then back to a string in the format you want, which seems a bit pointless? — Alex Poole, May 08 '17 at 10:27

score 0 · Answer 1 · answered May 08 '17 at 10:28

The date doesnt have a format in Oracle. It's your client. If you want to modify the default format in the current session, you can use:

alter session set nls_date_format='yyyymmdd'

If you want to convert the date into a format on adhoc basis, you can use to_char with format string:

select to_char(date_col, 'yyyymmdd') from table;

1 Answers1