Examine the structure and data of the CUST_TRANS table:
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-mon-rr in the CUST_TRANS table.
Which 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;
why is B incorrect?
I think transdate = ’01-01-07′; in this date is in charater format. Implicit conversion to date data type wont happens
In this date ’01-01-07′ month is as a number but default format is dd-mon-rr. For that reason implicit conversion will not take place.
why is D correct? does implicit conversion happens for ’01-JANUARY-07′ ?
if the default format is dd-mon-rr, then how is ’01-JANUARY-07′ correct?
Character data can be implicitly converted to date if the string conforms to the following date patterns
[D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY]
the separators can be punctuation marks, spaces and tabs
SQL> SELECT 1 FROM DUAL WHERE SYSDATE>’01-march-1999′;
1
———-
1
SQL> SELECT 1 FROM DUAL WHERE SYSDATE>’01 march/2008′;
1
———-
1
SQL> SELECT 1 FROM DUAL WHERE SYSDATE>’01.march,2008′;
1
———-
1
Still implicit conversion would take place. If you would write
for example ‘JANUARY-01-07’ then it does not work.
why is A corect? can you add a number to a date?
Yes, you can.
How can I create a (date) field which stored default date format will be dd-mm-rr?
Because I tried:
select employee_id,last_name,hire_date from employees where hire_date=’17-06-03′; — and it works
EMPLOYEE_ID LAST_NAME HIRE_DATE
———– ————————- ———
100 King 17/06/03
I use 11gi XE on Windows 7 and my system has format date dd/mm/yyyy.
can any one tell why A is correct & E is incorrect ?
A is correct because ’10’ is implicity converted to 10. So transdate + 10 adds 10 days on transdate.
E should be custno || ‘A’ or CONCAT(custno, ‘A’).
custno + ‘A’ gives “Invalid number” error.
https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm
IF ‘NLS_DATE_FORMAT’ = DD-MM-RR CORRECT QUESTIONS -> A,B,C,D
IF ‘NLS_DATE_FORMAT’ = DD-MON-RR CORRECT QUESTIONS -> A,C,D
SQL> select value from v$nls_parameters where parameter=’NLS_DATE_FORMAT’;
VALUE
—————————————————————-
DD-MM-RR
A)
SQL> SELECT hire_date + ’10’ FROM employees;
HIRE_DAT
——–
27-09-87
27-02-96
B)
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-01-90′;
FIRST_NAME HIRE_DAT
——————– ——–
Alexander 03-01-90
C)
SQL> SELECT first_name FROM employees WHERE custno > ’11’;
FIRST_NAME
——————–
eva
D)
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-JAN-90′;
FIRST_NAME HIRE_DAT
——————– ——–
Alexander 03-01-90
OR
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-JANEIRO-90′;
FIRST_NAME HIRE_DAT
——————– ——–
Alexander 03-01-90
SQL> select value from v$nls_parameters where parameter=’NLS_DATE_FORMAT’;
VALUE
—————————————————————-
DD-MON-RR
A)
SQL> SELECT hire_date + ’10’ FROM employees;
HIRE_DATE
———
27-SET-87
27-FEV-96
B)
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-01-90′;
SELECT first_name,hire_date FROM employees WHERE hire_date=’03-01-90′
*
ERRO na linha 1:
ORA-01843: nÒo Ú um mÛs vßlido
C)
SQL> SELECT first_name FROM employees WHERE custno > ’11’;
FIRST_NAME
——————–
eva
D)
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-JAN-90′;
FIRST_NAME HIRE_DATE
——————– ———
Alexander 03-JAN-90
OR
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-JANEIRO-90′;
FIRST_NAME HIRE_DATE
——————– ———
Alexander 03-JAN-90