OCP-1Z0-051 第92题 日期格式
一、原题View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.
Which two queries give the correct result? (Choose two.)
A. SELECT promo_name, TO_CHAR(promo_end_date,'Day')|| ', '||
TO_CHAR(promo_end_date,'Month') ' '
TO_CHAR(promo_end_date,'DD, YYYY') AS last_day
FROM promotions;
B. SELECT promo_name,TO_CHAR (promo_end_date,'fxDay')|| ', '||
TO_CHAR(promo_end_date,'fxMonth')|| ' '||
TO_CHAR(promo_end_date,'fxDD, YYYY') AS last_day
FROM promotions;
C. SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ||', '||
TRIM(TO_CHAR(promo_end_date,'Month')) ||' '||
TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day
FROM promotions;
D. SELECT promo_name,TO_CHAR(promo_end_date,'fmDay')||','||
TO_CHAR(promo_end_date,'fmMonth')||' '||
TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day
FROM promotions;
答案：CD
二、题目翻译
下面是 PROMOTIONS表的PROMO_NAME and PROMO_END_DATE列的数据 与 PROMO_END_DATE输出的所需格式：
下面哪两个查询能给出正确结果？（选择两个）
三、题目解析
A选项不正确，都是默认的，显示长度一样，即显示结果会对齐显示，没有去掉多余的空格。
B选项不正确，fx加到此处没什么作用,fx为精确匹配格式，和A选项的结果类似，也是会对齐显示，有多余的空格。
C选项正确，使用trim函数去掉左右多余的空格。
D选项正确，使用fm去掉多余的空格。
fm的详细使用方法，详见:
http://blog.iyunv.com/holly2008/article/details/25213993
四、测试
这里不再另外建表，使用oracle自带的测试表emp表，其中有一列hiredate列，和上面的格式类似。
测试ABCD四个选项的结果如下：
SQL> select ename,hiredate from emp;
ENAME HIREDATE
-------------------- ------------
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81
MARTIN 28-SEP-81
BLAKE 01-MAY-81
CLARK 09-JUN-81
SCOTT 19-APR-87
KING 17-NOV-81
TURNER 08-SEP-81
ADAMS 23-MAY-87
JAMES 03-DEC-81
FORD 03-DEC-81
MILLER 23-JAN-82
14 rows selected.
SQL> col last_day for a30
SQL> SELECT ename, TO_CHAR(hiredate,'Day')|| ', '||
2TO_CHAR(hiredate,'Month')|| ' '||
3TO_CHAR(hiredate,'DD, YYYY') AS last_day
4FROM emp;
ENAME LAST_DAY
-------------------- ------------------------------
SMITH Wednesday, December17, 1980
ALLEN Friday , February20, 1981
WARD Sunday , February22, 1981
JONES Thursday , April 02, 1981
MARTIN Monday , September 28, 1981
BLAKE Friday , May 01, 1981
CLARK Tuesday, June 09, 1981
SCOTT Sunday , April 19, 1987
KING Tuesday, November17, 1981
TURNER Tuesday, September 08, 1981
ADAMS Saturday , May 23, 1987
JAMES Thursday , December03, 1981
FORD Thursday , December03, 1981
MILLER Saturday , January 23, 1982
14 rows selected.
SQL> SELECT ename,TO_CHAR (hiredate,'fxDay')|| ', '||
2TO_CHAR(hiredate,'fxMonth')|| ' '||
3TO_CHAR(hiredate,'fxDD, YYYY') AS last_day
4FROM emp;
ENAME LAST_DAY
-------------------- ------------------------------
SMITH Wednesday, December17, 1980
ALLEN Friday , February20, 1981
WARD Sunday , February22, 1981
JONES Thursday , April 02, 1981
MARTIN Monday , September 28, 1981
BLAKE Friday , May 01, 1981
CLARK Tuesday, June 09, 1981
SCOTT Sunday , April 19, 1987
KING Tuesday, November17, 1981
TURNER Tuesday, September 08, 1981
ADAMS Saturday , May 23, 1987
JAMES Thursday , December03, 1981
FORD Thursday , December03, 1981
MILLER Saturday , January 23, 1982
14 rows selected.
SQL> SELECT ename, TRIM(TO_CHAR(hiredate,'Day')) ||', '||
2TRIM(TO_CHAR(hiredate,'Month')) ||' '||
3TRIM(TO_CHAR(hiredate,'DD, YYYY')) AS last_day
4FROM emp;
ENAME LAST_DAY
-------------------- ------------------------------
SMITH Wednesday, December 17, 1980
ALLEN Friday, February 20, 1981
WARD Sunday, February 22, 1981
JONES Thursday, April 02, 1981
MARTIN Monday, September 28, 1981
BLAKE Friday, May 01, 1981
CLARK Tuesday, June 09, 1981
SCOTT Sunday, April 19, 1987
KING Tuesday, November 17, 1981
TURNER Tuesday, September 08, 1981
ADAMS Saturday, May 23, 1987
JAMES Thursday, December 03, 1981
FORD Thursday, December 03, 1981
MILLER Saturday, January 23, 1982
14 rows selected.
SQL> SELECT ename,TO_CHAR(hiredate,'fmDay')||','||
2TO_CHAR(hiredate,'fmMonth')||' '||
3TO_CHAR(hiredate,'fmDD, YYYY') AS last_day
4FROM emp;
ENAME LAST_DAY
-------------------- ------------------------------
SMITH Wednesday,December 17, 1980
ALLEN Friday,February 20, 1981
WARD Sunday,February 22, 1981
JONES Thursday,April 2, 1981
MARTIN Monday,September 28, 1981
BLAKE Friday,May 1, 1981
CLARK Tuesday,June 9, 1981
SCOTT Sunday,April 19, 1987
KING Tuesday,November 17, 1981
TURNER Tuesday,September 8, 1981
ADAMS Saturday,May 23, 1987
JAMES Thursday,December 3, 1981
FORD Thursday,December 3, 1981
MILLER Saturday,January 23, 1982
14 rows selected.
版权声明：本文为博主原创文章，未经博主允许不得转载。
页:
[1]