错误sql

来源：互联网发布：北京行知打工子弟学校编辑：程序博客网时间：2024/06/08 10:24

SELECT to_char(to_date('2012-12-26 22:22:44', 'yyyy-mm-dd hh24:mi:ss'),
'MMDDYYYY:HH24:MI:SS') date1,
to_char(to_date('2012-12-27 22:22:44', 'yyyy-mm-dd hh24:mi:ss'),
'MMDDYYYY:HH24:MI:SS') date2,
　　

trunc(86400 * (date2 - date1)) - 60 * trunc(86400 * (date2 - date1) / 60) seconds,
　　

trunc(86400 * (date2 - date1) / 60) - 60 * trunc(86400 * (date2 - date1) / 60 / 60) minutes,
　　

trunc(86400 * (date2 - date1) / 60 / 60) - 24 * trunc(86400 * (date2 - date1) / 60 / 60 / 24) hours,
　　

trunc(86400 * (date2 - date1) / 60 / 60 / 24) days　,

trunc(86400 * (date2 - date1) / 60 / 60 / 24 / 7) weeks　　

FROM dual;

SELECT '06/20/2003:16:55:14:000000' time1,　'07/08/2003:11:22:57:000000'　time2,　　

substr((time2-time1),instr((time2-time1),' ')+7,2)　　　seconds,　　

substr((time2-time1),instr((time2-time1),' ')+4,2)　　　minutes,　　

substr((time2-time1),instr((time2-time1),' ')+1,2)　　　hours,　　

trunc(to_number(substr((time2-time1),1,instr(time2-time1,' '))))　 days,　　

trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks

FROM dual;

SELECT

60 * trunc(86400 * (date2 - date1) / 60) seconds,
　　

trunc(86400 * (date2 - date1) / 60) - 60 * trunc(86400 * (date2 - date1) / 60 / 60) minutes,
　　

trunc(86400 * (date2 - date1) / 60 / 60) - 24 * trunc(86400 * (date2 - date1) / 60 / 60 / 24) hours,
　　

trunc(86400 * (date2 - date1) / 60 / 60 / 24) days　,

trunc(86400 * (date2 - date1) / 60 / 60 / 24 / 7) weeks　　

FROM ( select to_date('2012-12-26 22:22:42', 'yyyy-mm-dd hh24:mi:ss') date1,
to_date('2012-12-27 22:22:44', 'yyyy-mm-dd hh24:mi:ss') date2 from dual) s

http://www.cnblogs.com/lz-cswb/archive/2012/04/29/2476329.html