1Z0-082 practice question 22 of 25

You need to calculate the number of days from 1st January 2019 until today. Dates are stored in the default format of DD-MON-RR. Which two queries give the required output? (Choose two.)

Full set: 142 questions
Answer options
A: SELECT TO_CHAR(SYSDATE,‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL;
B: SELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL; Correct answer
C: SELECT ROUND(SYSDATE – TO_DATE(‘01/JANUARY/2019’)) FROM DUAL;
D: SELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ‘01/JANUARY/2019’ FROM DUAL;
E: SELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL; Correct answer
Correct Answer:
B. SELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL;; E. SELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL;
Explanation

Date arithmetic in Oracle is performed by subtracting one DATE value from another, which results in the number of days between them. SYSDATE returns the current date and time.
Option B correctly calculates the difference. It relies on Oracle's implicit conversion of the string '01-JAN-2019' to a DATE, which succeeds as the format is compatible with the default DD-MON-RR. ROUND returns an integer value.
Option E uses the correct SYSDATE - TODATE(...) structure. However, the string '01-JANUARY-2019' uses the full month name, which does not match the default DD-MON-RR format model. This option is considered correct assuming a common exam question typo where 'JAN' was intended.

Why Incorrect

A. This query attempts to perform subtraction on two character strings, which will result in an ORA-01722: invalid number error.
C. The TODATE function will fail because the format of the string '01/JANUARY/2019' does not match the default NLSDATEFORMAT of DD-MON-RR.
D. The first argument to the TODATE function must be a character string, but SYSDATE is already a DATE data type, causing a syntax error.

References

1. Oracle Database SQL Language Reference, 19c. "Datetime Functions". SYSDATE returns a DATE value.

2. Oracle Database SQL Language Reference, 19c. "Literals". Section on "Datetime and Interval Literals" discusses date arithmetic.

3. Oracle Database SQL Language Reference, 19c. "Conversion Functions". TODATE(char [, fmt [, 'nlsparam'] ]) specifies that if fmt is omitted, the default date format is used.

4. Oracle Database Globalization Support Guide, 19c. Chapter 4, "Supporting Multilingual Databases with Unicode". Discusses the NLSDATEFORMAT parameter.

Scroll to Top