CODEWITHSUNDEEP
sqloracle-databaseto-char
Erik
Erik

Reputation: 396

TO_ CHAR in oracle

I am using oracle 11G and I know TO_CHAR is used to convert data into character and this query will work perfectly to get the day of the particular date:

SELECT TO_CHAR (DATE '2019-08-15', 'Day') FROM DUAL;

But this won’t work:

SELECT TO_CHAR (DATE '15-08-2019', 'Day') FROM DUAL;

ERROR: ORA-01847: day of month must be between 1 and last day of month

SELECT TO_CHAR (DATE '15-aug-19', 'Day') FROM DUAL;

ERROR: ORA-01843: not a valid month

Why the above won’t work. I have checked NLS_DATE_FORMAT it displays “DD-MON-RR”. But in first query it’s “YYYY-MM-DD”.

What am I missing here?

Upvotes: 0

Views: 728

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

Because YYYY-MM-DD is the string literal format in date'YYYY-MM-DD' with respect to ANSI 92 SQL standard.

For other format types, you need to convert explicitly, such as to_date('15-08-2019','dd-mm-yyyy')

Upvotes: 4

Hijesh V L
Hijesh V L

Reputation: 269

This issue is purely due to your NLS settings. if you run below query you can see the current NLS parameter values for your database

SELECT * FROM NLS_DATABASE_PARAMETERS

if you want to change your nls parameter for date please run below query 

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYYY';

Upvotes: 0

Related Questions