st
You need to calculate the number of days from 1
January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output? (Choose two.)
st
You need to calculate the number of days from 1
January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output? (Choose two.)
To calculate the number of days from 1st January 2019 until today using SQL queries, we need to consider the correct formatting and conversion functions. SQL uses date functions to manage and manipulate date values. The correct approach involves using the TO_DATE function to convert a string date to a DATE datatype and then performing arithmetic operations. Option C correctly uses TO_DATE to convert the date string and then ROUND to get the number of days. Similarly, Option E correctly uses TO_DATE for conversion and directly subtracts it from SYSDATE to calculate the days. Other options fail due to incorrect formatting or invalid operations on date strings directly.
C and E
A - false; query <SELECT TO_CHAR(sysdate,'DD-MON-YYYY') - '01-JAN-2019' FROM dual;> returns ORA-01722: invalid number B - false; query <SELECT ROUND(sysdate - '01-JAN-2019') FROM dual;> returns ORA-01722: invalid number C - true; query <SELECT ROUND(sysdate - TO_DATE('01/JANUARY/2019')) FROM dual;> returns number of days as natural number D - false; query <SELECT TO_DATE(sysdate,'DD/MONTN/YYYY') - '01/JANUARY/2019' FROM dual;> returns ORA-01821: date format not recognized E - true; query <SELECT sysdate - TO_DATE('01/JANUARY/2019') FROM dual;> returns number of days as floating number
The Answer: C, E The dates in the select '01-jan-2019' have to be converted with To_Date
The Options should be: A. SELECT TO_CHAR (SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL; B. SELECT ROUND (SYSDATE – ’01-JAN-2019’) FROM DUAL; 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; C and E are correct
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL; [Error] Execution (16: 44): ORA-01722: numero non valido; SELECT ROUND(SYSDATE - '01-gen-2019') FROM DUAL; [Error] Execution (16: 44): ORA-01722: numero non valido; SELECT ROUND(SYSDATE - TO_DATE('01/gen/2019')) FROM DUAL; 1576; SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/gennaio/2019' FROM DUAL; [Error] Execution (16: 44): ORA-01722: numero non valido; SELECT SYSDATE - TO_DATE('01-gennaio-2019') FROM DUAL; 1575,681400462962962962962962962962962963