本篇文章主要介绍了 oracle 日期格式,详细的介绍了日期的转换和计算,有兴趣的可以了解一下。
Oracle Database,又名 Oracle RDBMS,或简称 Oracle。是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。
to_date("要转换的字符串","转换的格式") 两个参数的格式必须匹配,否则会报错。
即按照第二个参数的格式解释第一个参数。
to_char(日期," 转换格式 ") 即把给定的日期按照" 转换格式 " 转换。
转换的格式:
表示 year 的:y 表示年的最后一位 yy 表示年的最后 2 位 yyy 表示年的最后 3 位 yyyy 用 4 位数表示年
表示 month 的:mm 用 2 位数字表示月;mon 用简写形式 比如 11 月或者 nov ;month 用全称 比如 11 月或者 november
表示 day 的:dd 表示当月第几天;ddd 表示当年第几天;dy 当周第几天 简写 比如星期五或者 fri;day 当周第几天 全写
比如星期五或者 friday。
表示 hour 的:hh 2 位数表示小时 12 进制; hh24 2 位数表示小时 24 小时
表示 minute 的:mi 2 位数表示分钟
表示 second 的:ss 2 位数表示秒 60 进制
表示季度的:q 一位数 表示季度 (1-4)
另外还有 ww 用来表示当年第几周 w 用来表示当月第几周。
24 小时制下的时间范围:00:00:00-23:59:59
12 小时制下的时间范围:1:00:00-12:59:59
比如:
- select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual //显示:08-11-07 13:22:42
- select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dual //显示:2005-12-25 13:25:59
而如果把上式写作:select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh:mi:ss') from dual,则会报错,因为小时 hh 是 12 进制,13 为非法输入,不能匹配。
补充:
当前时间减去 7 分钟的时间
- select sysdate,
- sysdate - interval '7'MINUTE from dual
当前时间减去 7 小时的时间
- select sysdate - interval '7' hour from dual
当前时间减去 7 天的时间
- select sysdate - interval '7' day from dual
当前时间减去 7 月的时间
- select sysdate,
- sysdate - interval '7'month from dual
当前时间减去 7 年的时间
- select sysdate,
- sysdate - interval '7'year from dual
时间间隔乘以一个数字
- select sysdate,
- sysdate - 8 * interval '7'hour from dual
Dual 伪列
含义解释:
Dual 是 Oracle 中的一个实际存在的表,任何用户均可读取,常用在没有目标表的 select 语句块中。
比如,我要获得系统时间,则用 "select sysdate from dual" 则返回系统当前的时间:2008-11-07 9:32:49,不同系统可能返回日期的格式不一样。"select user from dual" 则返回当前连接的用户。如果是 "select 1+2 from dual",则返回结果:3
TO_DATE 格式 (以时间: 2007-11-02 13:45:25 为例)
Year:
yy two digits 两位年 显示值: 07
yyy three digits 三位年 显示值: 007
yyyy four digits 四位年 显示值: 2007
Month:
mm number 两位月 显示值: 11
mon abbreviated 字符集表示 显示值: 11 月, 若是英文版, 显示 nov
month spelled out 字符集表示 显示值: 11 月, 若是英文版, 显示 november
Day:
dd number 当月第几天 显示值: 02
ddd number 当年第几天 显示值: 02
dy abbreviated 当周第几天简写 显示值: 星期五, 若是英文版, 显示 fri
day spelled out 当周第几天全写 显示值: 星期五, 若是英文版, 显示 friday
ddspth spelled out, ordinal twelfth
Hour:
hh two digits 12 小时进制 显示值: 01
hh24 two digits 24 小时进制 显示值: 13
Minute:
mi two digits 60 进制 显示值: 45
Second:
ss two digits 60 进制 显示值: 25
其它
Q digit 季度 显示值: 4
WW digit 当年第几周 显示值: 44
W digit 当月第几周 显示值: 1
24 小时格式下时间范围为: 0:00:00 - 23:59:59....
12 小时格式下时间范围为: 1:00:00 - 12:59:59 ....
1. 日期和字符转换函数用法(to_date,to_char)
- select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串
- select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年
- select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月
- select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日
- select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时
- select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分
- select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒
- select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual//
2.
- select to_char( to_date(222,'J'),'Jsp') from dual
显示 Two Hundred Twenty-Two
3. 求某天是星期几
- select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
星期一
- select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
设置日期语言
- ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
- TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4. 两个日期间的天数
- select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
5. 时间为 null 的用法
- select id, active_date from table1
- UNION
- select 1, TO_DATE(null) from dual;
注意要用 TO_DATE(null)
6. 月份差
- a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那么 12 月 31 号中午 12 点之后和 12 月 1 号的 12 点之前是不包含在这个范围之内的。
所以,当时间需要精确的时候,觉得 to_char 还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的 ORACLE 字符集的类型, 比如: US7ASCII, date 格式的类型就是: '01-Jan-01'
- alter system set NLS_DATE_LANGUAGE = American
- alter session set NLS_DATE_LANGUAGE = American
或者在 to_date 中写
- select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了 NLS_DATE_LANGUAGE,当然还有很多,
可查看
- select * from nls_session_parameters
- select * from V$NLS_PARAMETERS
8. 查找 2002-02-28 至 2002-02-01 间除星期一和七的天数
在前后分别调用 DBMS_UTILITY.GET_TIME, 让后将结果相减 (得到的是 1/100 秒, 而不是毫秒).
- select count(*)
- from ( select rownum-1 rnum
- from all_objects
- where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
- 02-01','yyyy-mm-dd')+1
- )
- where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
- not in ( '1', '7' )
9. 查找月份
- select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
- select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1.03225806451613
10. Next_day 的用法
- Next_day(date, day)
Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D
11
- select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意:第一条记录的 TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
- create or replace function sys_date return date is
- begin
- return sysdate;
- end;
- select to_char(sys_date,'hh:mi:ss') from all_objects;
12. 获得小时数
extract() 找出日期或间隔值的字段值
- SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
- SQL> select sysdate ,to_char(sysdate,'hh') from dual;
- SYSDATE TO_CHAR(SYSDATE,'HH')
- -------------------- ---------------------
- 2003-10-13 19:35:21 07
- SQL> select sysdate ,to_char(sysdate,'hh24') from dual;
- SYSDATE TO_CHAR(SYSDATE,'HH24')
- -------------------- -----------------------
- 2003-10-13 19:35:21 19
13. 年月日的处理
- select older_date,
- newer_date,
- years,
- months,
- abs(
- trunc(
- newer_date-
- add_months( older_date,years*12+months )
- )
- ) days
- from ( select
- trunc(months_between( newer_date, older_date )/12) YEARS,
- mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,
- newer_date,
- older_date
- from (
- select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date
- from emp
- )
- )
14. 处理月份天数不定的办法
- select to_char(add_months(last_day(sysdate) + 1, -2), 'yyyymmdd'),
- last_day(sysdate) from dual
16. 找出今年的天数
- select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
闰年的处理方法
- to_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )
如果是 28 就不是闰年
17.yyyy 与 rrrr 的区别
- 'YYYY99 TO_C
- ------- ----
- yyyy 99 0099
- rrrr 99 1999
- yyyy 01 0001
- rrrr 01 2001
18. 不同时区的处理
- select to_char(NEW_TIME(sysdate, 'GMT', 'EST'), 'dd/mm/yyyy hh:mi:ss'),
- sysdate from dual;
19.5 秒钟一个间隔
- Select TO_DATE(FLOOR(TO_CHAR(sysdate, 'SSSSS') / 300) * 300, 'SSSSS'),
- TO_CHAR(sysdate, 'SSSSS') from dual
2002-11-1 9:55:00 35786
SSSSS 表示 5 位秒数
20. 一年的第几天
- select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 10:03:51
21. 计算小时, 分, 秒, 毫秒
- select Days, A,
- TRUNC(A*24) Hours,
- TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
- TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
- TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
- from
- (
- select
- trunc(sysdate) Days,
- sysdate - trunc(sysdate) A
- from dual
- )
- select * from tabname
- order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');
- //
- floor((date2-date1) /365) 作为年
- floor((date2-date1, 365) /30) 作为月
- d(mod(date2-date1, 365), 30)作为日.
23.next_day 函数 返回下个星期的日期, day 为 1-7 或星期日 - 星期六, 1 表示星期日
next_day(sysdate,6) 是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
- select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
日期 返回的是天 然后 转换为 ss
24,round[舍入到最接近的日期](day: 舍入到最接近的星期日)
- select sysdate S1,
- round(sysdate) S2 ,
- round(sysdate,'year') YEAR,
- round(sysdate,'month') MONTH ,
- round(sysdate,'day') DAY from dual
25,trunc[截断到最接近的日期, 单位为天] , 返回的是日期类型
- select sysdate S1,
- trunc(sysdate) S2,
- //返回当前日期,无时分秒
- trunc(sysdate, 'year') YEAR,
- //返回当前年的1月1日,无时分秒
- trunc(sysdate, 'month') MONTH,
- //返回当前月的1日,无时分秒
- trunc(sysdate, 'day') DAY //返回当前星期的星期天,无时分秒
- from dual
26, 返回日期列表中最晚日期
- select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
27. 计算时间差
注: oracle 时间差是以天数为单位, 所以换算成年月, 日
- select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual //时间差-年
- select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //时间差-月
- select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual //时间差-天
- select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual //时间差-时
- select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual //时间差-分
- select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒
28. 更新时间
注: oracle 时间加减是以天数为单位, 设改变量为 n, 所以换算成年月, 日
- select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
- to_char(sysdate + n * 365, 'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-年
- select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
- add_months(sysdate, n) as newTime from dual //改变时间-月
- select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
- to_char(sysdate + n, 'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-日
- select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
- to_char(sysdate + n / 24, 'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-时
- select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
- to_char(sysdate + n / 24 / 60, 'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-分
- select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
- to_char(sysdate + n / 24 / 60 / 60, 'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-秒
29. 查找月的第一天, 最后一天
- SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
- Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
- Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
- LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
- FROM dual;
来源: