日期格式转换
很多开发运维人员都会为了字符转日期 日期转字符，日期格式化诸如此类的问题发愁
下面列出一些关于日期转换的例子集合：
以emp表的hiredate（date类型）为例：
SCOTT@ prod>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
已选择14行。
SCOTT@ prod>desc emp;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
两种日期表达方式：
SCOTT@ prod>select ename,hiredate, to_char(hiredate, 'DD-MON-YY') month_hired,to_char(hiredate,'FmDdspth "of" Month,Year') dd from emp;
ENAME HIREDATE MONTH_HIRED DD
------------------------------ ------------------- ------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SMITH 1980-12-17 00:00:00 17-12月-80 Seventeenth of 12月,Nineteen Eighty
ALLEN 1981-02-20 00:00:00 20-2月 -81 Twentieth of 2月,Nineteen Eighty-One
WARD 1981-02-22 00:00:00 22-2月 -81 Twenty-Second of 2月,Nineteen Eighty-One
JONES 1981-04-02 00:00:00 02-4月 -81 Second of 4月,Nineteen Eighty-One
MARTIN 1981-09-28 00:00:00 28-9月 -81 Twenty-Eighth of 9月,Nineteen Eighty-One
BLAKE 1981-05-01 00:00:00 01-5月 -81 First of 5月,Nineteen Eighty-One
CLARK 1981-06-09 00:00:00 09-6月 -81 Ninth of 6月,Nineteen Eighty-One
SCOTT 1987-04-19 00:00:00 19-4月 -87 Nineteenth of 4月,Nineteen Eighty-Seven
KING 1981-11-17 00:00:00 17-11月-81 Seventeenth of 11月,Nineteen Eighty-One
TURNER 1981-09-08 00:00:00 08-9月 -81 Eighth of 9月,Nineteen Eighty-One
ADAMS 1987-05-23 00:00:00 23-5月 -87 Twenty-Third of 5月,Nineteen Eighty-Seven
JAMES 1981-12-03 00:00:00 03-12月-81 Third of 12月,Nineteen Eighty-One
FORD 1981-12-03 00:00:00 03-12月-81 Third of 12月,Nineteen Eighty-One
MILLER 1982-01-23 00:00:00 23-1月 -82 Twenty-Third of 1月,Nineteen Eighty-Two
已选择14行。
SCOTT@ prod>日期是字符类型，转换成想要的日期格式：
2009-10-10 字段是char类型如何转换成目标格式
SCOTT@ prod>create table D_CHAR_T_DAT (Riqi char(30),hirdate number);
表已创建。
SCOTT@ prod>insert into D_CHAR_T_DAT values('2009-10-10',20200102);
已创建 1 行。
SCOTT@ prod>commit;
提交完成。
SCOTT@ prod>select * from D_CHAR_T_DAT;
RIQI HIRDATE
------------------------------------------------------------------------------------------ ----------
2009-10-10 20200102
SCOTT@ prod>SELECT TO_CHAR(TO_DATE(Riqi),'FmDdspth "of" Month,Year') , to_date(HIRDATE) from D_CHAR_T_DAT;
TO_CHAR(TO_DATE(RIQI),'FMDDSPTH"OF"MONTH,YEAR') TO_DATE(HIRDATE)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------
Tenth of 10月,Two Thousand Nine 2020-01-02 00:00:00
SCOTT@ prod>上述内容为OCP题库内容
下面是转载的内容关于日期处理
获取时间：从上至下 年月日时分秒
SCOTT@ prod>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS')
---------------------------------------------------------
2021-03-30 15:22:26
SCOTT@ prod>select to_char(sysdate,'yyyy') from dual;
TO_CHAR(SYSD
------------
2021
SCOTT@ prod>select to_char(sysdate,'mm') from dual;
TO_CHA
------
03
SCOTT@ prod>select to_char(sysdate,'dd') from dual;
TO_CHA
------
30
SCOTT@ prod>select to_char(sysdate,'hh24') from dual;
TO_CHA
------
15
SCOTT@ prod>select to_char(sysdate,'mi') from dual;
TO_CHA
------
22
SCOTT@ prod>select to_char(sysdate,'ss') from dual;
TO_CHA
------
26
SCOTT@ prod>求某一天是周几
SCOTT@ prod>select to_char(to_date('2020-08-26','yyyy-mm-dd'),'day') from dual;
TO_CHAR(TO_DATE('2
------------------
星期三SCOTT@ prod>select to_char(to_date('2020-08-26','yyyy-mm-dd'),'day', 'NLS_DATE_LANGUAGE = American') from dual;
TO_CHAR(TO_DATE('2020-08-26
---------------------------
wednesday求两个日期中的天数（除去周6 周日）
SCOTT@ prod>select count(*)
2 from ( select rownum-1 rnum
3 from all_objects
4 where rownum <= to_date('2020-02-28','yyyy-mm-dd') - to_date('2020-
5 02-01','yyyy-mm-dd')+1
6 )
7 where to_char( to_date('2020-02-01','yyyy-mm-dd')+rnum-1, 'D' )
8 not in ( '6', '7' ) ;
COUNT(*)
----------
20
SCOTT@ prod>查找月份
select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
//结果为：1
select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
//结果为：1.03225806451613
SCOTT@ prod>select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
MONTHS
----------
1
SCOTT@ prod>select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
MONTHS
----------
1.03225806
更为详细的日期处理方法：
转载 https://www.jb51.net/article/45591.htm
Jrojyun
2021-03-30
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
