to_date、to_char及格式化大小寫 nvl nvl2 nullif

Examine the structure of the TRANSACTIONS table:
name          Null          Type
TRANS_ID     NOT NULL     NUMBER(3) 
The safer , easier way to help you pass any IT exams.
54 / 99
CUST_NAME                  VARCHAR2(30)
TRANS_DATE                 DATE
TRANS_AMT                  NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend.
Evaluate the following two queries:
SQL>SELECT TRANS_DATE,CASE
WHEN TRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend'
ELSE 'weekday'
END "Day Type"
FROM transactions;
SQL>SELECT TRANS_DATE, CASE
WHEN TO_CHAR(trans_date,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY' THEN 'weekday'
ELSE  'weekend'
END "Day Type"FROM transactions;
Which statement is true regarding the above queries?
A. Both give wrong results.
B. Both give the correct  result.
C. Only the first query gives the correct result.
D. Only the  second query gives the correct result.

答案C

TO_CHAR是把日期或者數字轉換為字元，to_date把數字轉換成日期
SELECT to_date(SYSDATE,'hh24') FROM dual ---錯
SELECT to_date(SYSDATE,'hh24') FROM dual --對

SELECT to_char(SYSDATE,'day') FROM dual 輸出結果為小寫
SELECT to_char(SYSDATE,'DAY') FROM dual 輸出結果為大寫

星期是不能用BETWEEN取區間
SELECT to_char(t,'DAY') FROM t
 WHERE to_char(t,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY'

沒有結果返回


--------------------------------

NVL (expr1, expr2)->expr1為NULL，返回expr2；不為NULL，返回expr1。注意兩者的型別要一致
NVL2 (expr1, expr2, expr3) ->expr1不為NULL，返回expr2；為NULL，返回expr3。expr2和expr3型別不同的話，expr3會轉換為expr2的型別
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1

Examine the structure of the PROMOS table:
name              Null        Type
PROMO_ID          NOT NULL   NUMBER(3)
PROMO_NAME                   VARCHAR2(30)
PROMO_START_DATE NOT NULL   DATE
PROMO_END_DATE   NOT NULL   DATE
You want to display the list of promo names with the message 'Same Day' for promos that started and
ended on the same day.
Which query gives the correct output?
A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day')
FROM promos;
B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day')
FROM promos;
C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,'Same
Day')
FROM promos;
D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day')
FROM promos;
Answer: D