When the NLS_DATE_FORMAT is set to DD Mon YYYY, the database expects the date string to be in the format of day, abbreviated month name, and full year.
B. The query SELECT DATE '2019-01-01' FROM DUAL; correctly returns the value 1 Jan 2019 because the ANSI date literal DATE 'YYYY-MM-DD' is independent of the NLS_DATE_FORMAT parameter.
Option A is incorrect because the TO_DATE function requires the format model to match the string literal, which it does not in this case.
Option C is incorrect because TO_CHAR is used to convert a date to a string, not a string to a date.
Option D is incorrect because without specifying that the string is a date, the result is just a string and not a date value.
Option E is incorrect because TO_DATE without a format model relies on the NLS_DATE_FORMAT to interpret the string, and YYYY-MM-DD does not match DD Mon YYYY.
[Reference: Oracle Database SQL Language Reference 12c, Datatype Conversion - TO_DATE and ANSI Date Literals, ]
Contribute your Thoughts:
Chosen Answer:
This is a voting comment (?). You can switch to a simple comment. It is better to Upvote an existing comment if you don't have anything to add.
Submit