1Z0-071 practice question 15 of 35

In your session NLS_ DATE_ FORMAT is set to DD–MON_RR. Which two queries display the year as four digits?

Full set: 326 questions
Answer options
A: SELECT TO_DATE(TO_CHAR(SYSDATE,'MM/DD/YYYY'),'MM/DD/YYYY') FROM DUAL;
B: SELECT TO_CHAR (ADD_MONTHS (SYSDATE,6)) FROM DUAL;
C: SELECT TO_DATE (SYSDATE, 'RRRR-MM-DD') FROM DUAL;
D: SELECT TO_DATE (ADD_MONTHS(SYSDATE,6), 'dd-mon-yyyy') FROM DUAL;
E: SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL; Correct answer
F: SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL; Correct answer
Correct Answer:
E. SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL;; F. SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;
Explanation

TOCHAR with an explicit format mask overrides the session's NLSDATEFORMAT and returns a string with the specified format. Option E uses TOCHAR(SYSDATE, 'MM/DD/YYYY') which explicitly formats the date with a 4-digit year. Option F uses TOCHAR with 'dd-mon-yyyy' format, also displaying a 4-digit year. Options A, C, and D use TODATE which returns a DATE datatype, not a string, and when displayed will use the session's NLSDATEFORMAT (DD-MON-RR), showing only 2-digit years. Option B lacks a format mask, so it uses the default NLSDATEFORMAT.

Why Incorrect

A: TODATE returns DATE type, displayed using NLSDATEFORMAT (2-digit year). B: No format mask specified, uses NLSDATEFORMAT (2-digit year). C: TODATE returns DATE type, displayed using NLSDATEFORMAT (2-digit year). D: TODATE returns DATE type, displayed using NLSDATEFORMAT (2-digit year).
3. References:
- Oracle Database SQL Language Reference 19c, "TOCHAR (datetime)", Section 7-231
- Oracle Database Globalization Support Guide 19c, "Setting NLS Parameters", Section 3.4
- Oracle Database SQL Language Reference 19c, "Format Models", Section 2-69
- Oracle Database Concepts 19c, "Oracle Data Types", Section 2-8
- Oracle Database Administrator's Guide 19c, "Managing Globalization Support", Section 6-12

References

1. Oracle Database SQL Language Reference 19c, “TOCHAR (datetime)”, Table 5-17 (YYYY vs RR format elements).

2. Oracle Database Globalization Support Guide 19c, “NLSDATEFORMAT Parameter”, sec. 2.3.

Scroll to Top