How Do You Calculate Days from 1 Jan 2019 to SYSDATE in Oracle?
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.)
Community Votes
75% of anonymous learners picked answer CE. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
It tests DATE-minus-DATE arithmetic versus implicit conversion of a quoted date string under the default DD-MON-RR format; the trap is that a bare '01-JAN-2019' literal cannot simply be subtracted from SYSDATE.
Oracle returns the difference between two DATE values as a number of days, so SYSDATE - TO_DATE('01-JANUARY-2019') yields the days elapsed since 1 January 2019. This page confirms that options C and E are the two working queries and explains why the string-literal and TO_CHAR variants collapse into conversion errors.
Choosing B, which subtracts the raw string '01-JAN-2019' from SYSDATE and hopes Oracle will implicitly convert it. With NLS_DATE_FORMAT of DD-MON-RR the four-digit-year literal does not match the format, so the statement errors out instead of returning days, and the explicit TO_DATE used in C and E is what makes the arithmetic valid.
Community Discussion (5 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Both C and E perform the one arithmetic Oracle actually supports here: a DATE value minus a DATE value, which returns a NUMBER counted in days. In E,SYSDATE - TO_DATE('01-JANUARY-2019') converts the character literal into a DATE first, so the subtraction yields the elapsed days since 1 January 2019 (including the time-of-day fraction, e.g. 2153.62). C does the same computation but wraps it in ROUND so the output is a whole number of days, and it also supplies the required FROM DUAL clause. The conversion step is the decisive element in both: TO_DATE makes the literal a genuine DATE under the session's DD-MON-RR style, which is exactly what the stem's format note is pointing at. (The en dash shown between SYSDATE and TO_DATE in option C is a web-rendering artifact; the real operator is the minus sign.)Why the Other Options Are Wrong
Option A calls TO_CHAR(SYSDATE, 'DD-MON-YYYY'), which returns a character string, and then tries to subtract another string from it — Oracle attempts numeric conversion and raises ORA-01722 (invalid number), and the statement has no table after FROM. Option B leaves '01-JAN-2019' as a string, expecting implicit conversion: because the session date format is DD-MON-RR the four-digit year does not match the format mask, so the statement fails with a conversion error rather than returning a numeric day count; no TO_DATE is used. Option D is doubly broken — it applies TO_DATE to SYSDATE itself with the mask 'DD/MONTH/YYYY', which first has to implicitly print SYSDATE in the NLS format before re-parsing it, and it then subtracts the bare literal '01/JANUARY/2019' as a string, so the arithmetic never gets two DATEs. Only C and E leave two real DATE values on either side of the operator.Community Comment Notes
JayaprasanthGurunathan reasons along the same lines as the explanation above: SYSDATE is a DATE, so date arithmetic is direct, while string literals such as '01-JAN-2019' must be converted with TO_DATE. Misi_Oracle flags the odd hyphen in option C, noting "i think there is a mistake.", which matches the rendering artifact described above rather than a real syntax flaw. yaya32 disagrees with the multi-select framing and argues E is "The only working query is E all of the others are not working." — true for a single-answer reading, but C reproduces the same DATE subtraction with ROUND and a FROM DUAL clause, so both satisfy the stem. HUGO2024 simply states "C, E is correct", and the recorded votes (75 for CE versus 25 for E alone) line up with that reading.Official Reference
Exam Strategy
For multi-select date questions, validate each expression for a legal DATE minus DATE subtraction: any option that leaves a quoted date string unprotected by TO_DATE, wraps SYSDATE inside TO_CHAR, or omits FROM DUAL drops out immediately. That filter alone removes A, B and D here and leaves exactly C and E, matching the 'Choose two' instruction.
Frequently Asked Questions
Why does option B fail if Oracle converts date strings implicitly?
Implicit conversion uses the session NLS_DATE_FORMAT of DD-MON-RR, and the literal '01-JAN-2019' carries a four-digit year, so Oracle raises ORA-01861 instead of subtracting.
Is ROUND required to get the number of days, as option C uses?
No. DATE subtraction already returns days as a number; C rounds the fractional value, while E returns it with the time-of-day fraction still attached, which is also accepted.