欢迎光临
我们一直在努力

sql 语句对时间操作的总结_数据库技巧

建站超值云服务器,限时71元/月

在SQL语句中,常常用会对时间(或日期)进行一些处理,下面是比较通用的一些语句:


延迟:


sysdate+(5/24/60/60)          在系统时间基础上延迟5秒


sysdate+5/24/60               在系统时间基础上延迟5分钟


sysdate+5/24                  在系统时间基础上延迟5小时


sysdate+5                     在系统时间基础上延迟5天


add_months(sysdate,-5)        在系统时间基础上延迟5月


add_months(sysdate,-5*12)     在系统时间基础上延迟5年


 


上月末的日期:


select last_day(add_months(sysdate, -1)) from dual;


 


本月的最后一秒:


select trunc(add_months(sysdate,1),MM) – 1/24/60/60 from dual


 


本周星期一的日期:


select trunc(sysdate,day)+1 from dual


 


年初至今的天数:


select ceil(sysdate – trunc(sysdate, year)) from dual;


 


今天是今年的第几周 :


select to_char(sysdate,fmww) from dual


 


今天是本月的第几周:


SELECT TO_CHAR(SYSDATE,WW) – TO_CHAR(TRUNC(SYSDATE,MM),WW) + 1 AS “weekOfMon” FROM dual


 


本月的天数


SELECT to_char(last_day(SYSDATE),dd) days FROM dual


 


今年的天数


select add_months(trunc(sysdate,year), 12) – trunc(sysdate,year) from dual


 


下个星期一的日期


SELECT Next_day(trunc(SYSDATE),monday) FROM dual


 


 


================================


 


计算工作日方法


 


create table t(s date,e date);


alter session set nls_date_format = yyyy-mm-dd;


insert into t values(2003-03-01,2003-03-03);


insert into t values(2003-03-02,2003-03-03);


insert into t values(2003-03-07,2003-03-08);


insert into t values(2003-03-07,2003-03-09);


insert into t values(2003-03-05,2003-03-07);


insert into t values(2003-02-01,2003-03-31);


 


— 这里假定日期都是不带时间的,否则在所有日期前加trunc即可。


select s,e,e-s+1 total_days, trunc((e-s+1)/7)*5 + length(replace(substr(01111100111110,to_char(s,d),mod(e-s+1,7)),0,)) work_days from t;


 


— drop table t;


 


 


========================================================


 


判断当前时间是上午下午还是晚上


 


SELECT CASE


WHEN to_number(to_char(SYSDATE,hh24)) BETWEEN 6 AND 11 THEN 上午


WHEN to_number(to_char(SYSDATE,hh24)) BETWEEN 11 AND 17 THEN 下午


WHEN to_number(to_char(SYSDATE,hh24)) BETWEEN 17 AND 21 THEN 晚上


END


FROM dual;


 


 


==========================================================


 


Oracle 中的一些处理日期


 


将数字转换为任意时间格式.如秒:需要转换为天/小时


SELECT to_char(floor(TRUNC(936000/(60*60))/24))||天||to_char(mod(TRUNC(936000/(60*60)),24))||小时 FROM DUAL


TO_DATE格式


Day:


dd number 12


dy abbreviated fri


day spelled out friday


ddspth spelled out, ordinal twelfth


Month:


mm number 03


mon abbreviated mar


month spelled out march


Year:


yy two digits 98


yyyy four digits 1998


 


24小时格式下时间范围为: 0:00:00 – 23:59:59….


12小时格式下时间范围为: 1:00:00 – 12:59:59 ….


 


1. 日期和字符转换函数


用法(to_date,to_char)


 


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.


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 )


 


查找2002-02-28至2002-02-01间除星期一和七的天数


在前后分别调用DBMS_UTILITY.GET_TIME, 然后将结果相减(得到的是1/100秒, 而不是毫秒).


 


9.


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


 


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. 获得小时数


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


 


TO_DATE(FL TO_CH


———- —–


2007-01-01 60368


 


SSSSS表示5位秒数


 


20. 一年的第几天


select TO_CHAR(SYSDATE,DDD),sysdate from dual


 


TO_ SYSDATE


— ———-


017 2007-01-17


 


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)  作为月


mod(mod(date2-date1, 365), 30) 作为日.


 


22.next_day函数


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 dual


日期 返回的是天 然后 转换为ss

赞(0)
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com 特别注意:本站所有转载文章言论不代表本站观点! 本站所提供的图片等素材,版权归原作者所有,如需使用,请与原作者联系。未经允许不得转载:IDC资讯中心 » sql 语句对时间操作的总结_数据库技巧
分享到: 更多 (0)