Reputation: 396
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
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
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