CUST_TRANS -
Name Null? Type
-------------- ----------------- ------------------
CUSTNO NOT NULL CHAR (2)
TRANSDATE DATE
TRANSAMT NUMBER (6, 2)
CUSTNO TRANSDATE TRANSAMT
------------- ----------------------- -----------------------
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)

    A. SELECT transdate   '10' FROM cust_trans;
    B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
    C. SELECT transamt FROM cust_trans WHERE custno > '11';
    D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
    E. SELECT custno   'A' FROM cust_trans WHERE transamt > 2000;

i am working on this question, and i created the table in sqldeveloper,i tried running the same code in B, and it works. so i am wondering why is answer B considered to be wrong.

INSERT ALL 
INTO CUST_TRANS VALUES (11, '01-jAN-07', 1000)
INTO CUST_TRANS VALUES (22, '01-FEB-07', 2000)
INTO CUST_TRANS VALUES (33, '01-MAR-07', 3000)

SELECT * FROM DUAL;

the table i made. the script i tried running

SELECT * FROM cust_trans WHERE transdate = '01-01-07'; 

it runs successfully in sqldeveloper, i am running the latest oracle version

CodePudding user response：

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:

  TRANSDATE '10'
  11-01-07

• 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:

  TRANSAMT
  5000

• 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

CodePudding user response：

You have something called nls_date_format. You can set this for your session by altering it.

When you set it to the format of your question like this:

ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mm-rr';

The B answer works just fine.

When you set it to, for example:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD MON YYYY';

It will not work.

DEMO

You can check how this parameter is set in your session with this query:

select *
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT';