Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
This statement is wrong! A DATE is a binary data type consisting of 7 bytes representing: century, year-of-century, month, day, hours, minutes and seconds. It is ALWAYS stored with all of those components and it is NEVER stored is any particular (human-readable) format as it is stored as binary values.
If you are storing date-like data in the format dd-mm-rr then you are storing them as strings and not as a DATE. If you are storing dates as a DATE then you cannot be storing them in a human-readable format.
If you want to see how Oracle stores the DATE then use:
SELECT DUMP(transdate) FROM cust_trans;
What the person who set the question probably means is that dd-mm-rr is the default format which Oracle uses for string-to-date and date-to-string conversions (which uses the NLS_DATE_FORMAT session parameter) and that some client applications (i.e. SQL*Plus and SQL Developer) use as the default display format when displaying dates. However, just because some session parameters allow for implicit conversion of your string does not mean that it is good practice.
It is also wrong to state that dd-mm-rr is the default date format as it is only the default for the NLS_DATE_FORMAT session parameter in certain territories (Algeria, Bahrain, India, Morocco, The Netherlands and Tunisia); in other places around the world it is not the default.
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
I tried running the same code in B, and it works. So I am wondering why is answer B considered to be wrong.
B. will work provided Oracle can perform an implicit cast from a string to a DATE and it does that using the NLS_DATE_FORMAT session.
The code is effectively the same as:
SELECT *
FROM cust_trans
WHERE transdate = TO_DATE(
'01-01-07',
( SELECT value
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT' )
);
If the NLS_DATE_FORMAT matches dd-mm-rr (or another format by the string-to-date conversion rules) then it will parse it.
If you do:
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mm-rr';
SELECT * FROM cust_trans WHERE transdate = '01-01-07'
Then the date will get parsed to 2007-01-01 00:00:00 but if you use:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT * FROM cust_trans WHERE transdate = '01-01-07'
Then the date will get parsed to 0001-01-07 00:00:00 which has an unexpected century and the year and days swapped.
If you use:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
SELECT * FROM cust_trans WHERE transdate = '01-01-07'
Then it does not parse the value and raises the exception ORA-01843: not a valid month.
db<>fiddle here
You should NEVER rely on implicit casts of strings to dates.
A better solution would be:
Use a date literal
SELECT * FROM cust_trans WHERE transdate = DATE '2007-01-01'
Use a timestamp literal
SELECT * FROM cust_trans WHERE transdate = TIMESTAMP '2007-01-01 00:00:00'
or, use an explicit string-to-date conversion
SELECT * FROM cust_trans WHERE transdate = TO_DATE('01-01-07', 'DD-MM-RR')
as none of these rely on an implicit cast between data types.
As for the answer to the question.
If the NLS_DATE_FORMAT is DD-MM-RR then four (not three) of the options are queries that will successfully parse and execute.
Given the setup:
CREATE TABLE cust_trans (
custno CHAR(2),
transdate DATE,
transamt NUMBER
);
INSERT INTO cust_trans (custno, transdate, transamt) VALUES ('25', DATE '2007-01-01', 5000);
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RR';
Then:
A. SELECT transdate + '10' FROM cust_trans;
Will perform an implicit cast from the string literal '10' to a number 10 and then will add 10 days to transdate and outputs:
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
Will perform an implicit cast from string-to-date and output:
| CUSTNO |
TRANSDATE |
TRANSAMT |
| 20 |
01-01-07 |
5000 |
C. SELECT transamt FROM cust_trans WHERE custno > '11';
Is valid and outputs:
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
Will perform an implicit cast from string-to-date and apply the string-to-date conversion rules that allow MM to also match the format models MON and MONTH and will output:
| CUSTNO |
TRANSDATE |
TRANSAMT |
| 20 |
01-01-07 |
5000 |
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;
Does not need to perform an implicit cast as transamt > 2000 is a valid filter. However, custno + 'A' is not valid as you cannot add a string to a date. If you wanted to concatenate strings then you need the || operator and not +. This will raise the exception:
ORA-01722: invalid number
As the + operator expects the second operand to be numeric and it is not.
db<>fiddle here
