CODEWITHSUNDEEP
oracle-databaseoracle-sqldevelopersqlplus
ARIS
ARIS

Reputation: 352

SQLPLus vs SQLDeveloper behavior

I'm experiencing a different behavior between SQLPlus and SQL Developer.

Example data:

create table test (
  INIT_DATE DATE
);

INSERT INTO test(INIT_DATE) values (sysdate);
COMMIT;

Now I run the following query (notice we're doing an unnecessary to_date because INIT_DATE is already a date):

select to_date(INIT_DATE, 'dd/mm/rrrr') from test;

The result is:

I found this answer, so in SQLDeveloper I changed NLS>Format Date to 'DD/MM/RR' and now SQLDeveloper return 20/09/16.

But, if in SQLDeveloper I change NLS to 'DD/MM/RR HH24:MI:SS' again, and I change the query mask to 'DD/MM/RR', SQLDeveloper return an error again:

select to_date(INIT_DATE, 'DD/MM/RR') from test;

Upvotes: 1

Views: 927

Answers (2)

AlexSmet
AlexSmet

Reputation: 2161

You try convert DATE to DATE through TO_DATE function, but TO_DATE function arguments are strings and as result Oracle convert INIT_DATE column to string and then pass this string into TO_DATE function.

If you use implicit conversion 'string to date' or 'date to string' , then Oracle use the default date format. In different environments default date format may be different.

Try to use an explicit conversion and an appropriate format. For example:

select to_date(to_char(INIT_DATE, 'dd/mm/rrrr'), 'dd/mm/rrrr') from test;

Upvotes: 1

Petr Pribyl
Petr Pribyl

Reputation: 3575

Use TO_CHAR instead of TO_DATE. TO_DATE function converts char argument in specific format given by second parameter to date value.

Your statement 

select to_date(INIT_DATE, 'DD/MM/RR') from test;

does first implicit conversion to char, because INIT_DATE is a date. This conversion is in nls default format, depending on your machine settings.

Upvotes: 2

Related Questions