1Z0-082 practice question 18 of 25

The orders table has a column ORDER_DATE of data type date. The default display format for a date Is DD-MON-RR. Which two where conditions demonstrate the correct usage of conversion functions?

Full set: 142 questions
Answer options
A: WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')
B: WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'JAN 20 2019' Correct answer
C: WHERE order_date > TO_DATE<ADD_MONTHS(SYSDATE, c) , 'MON DD YYYY')
D: WHERE order_date IN (TO_DATE ('Oct 21 2018', 'Mon DD YYYY'), TC_CHAR('Nov 21 2018', 'Mon DD YYYY'))
E: WHERE order date > TO DATE('JUL 10 2018', *MON DD YYYY') Correct answer
Correct Answer:
B. WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'JAN 20 2019'; E. WHERE order date > TO DATE('JUL 10 2018', *MON DD YYYY')
Explanation

The best practice for date comparisons is to compare columns of type DATE with values of type DATE. Option E correctly uses the TODATE function to convert a character string into a DATE data type for comparison, which is efficient and allows index usage. Option B demonstrates a syntactically correct usage of the TOCHAR function. It converts the orderdate column's value to a character string and compares it to a string literal. Although applying functions to a column in a WHERE clause can hinder performance by preventing index scans, it is a valid use of a conversion function.

Why Incorrect

A. This compares a DATE column to a VARCHAR2 value, relying on potentially ambiguous implicit data type conversion by Oracle, which is not a correct or reliable practice.
C. This option contains a syntax error (TODATE<...) and is logically flawed, as ADDMONTHS already returns a DATE, making a TODATE conversion redundant and incorrect.
D. This option has a typo (TCCHAR) and incorrectly mixes data types (DATE and VARCHAR2) within an IN list, which will lead to conversion errors.

References

1. Oracle Database SQL Language Reference, 19c, "TODATE". This section details the function for converting character strings to DATE values, as shown in option E.

2. Oracle Database SQL Language Reference, 19c, "TOCHAR (datetime)". This section explains the function for converting DATE values to character strings, as used in option B.

3. Oracle Database SQL Tuning Guide, 19c, "Follow these Guidelines for WHERE Clauses". The guide advises against applying functions to indexed columns, but acknowledges the syntax is valid.

Scroll to Top