求sql 我有两表示时间位的字段﹐类型为varchar2(5),值的格式为 08:10Start_time 为起始时间﹐ end_time 为结束时间 怎么查询出 结束时间-开始时间 如 :8:30-:7:50=0:40, 这种 样子怎么处理?数据库 8i 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 to_date(Start_time, 'hh24:mi')-to_date(end_time, 'hh24:mi') to_char(to_date(end_time, 'hh24:mi')-to_date(start_time, 'hh24:mi'),'hh24:mi') 用TIMESTAMP来做吧. 具體 不嫌麻烦的话,可以这样做:select trunc((to_date(end_time, 'hh24:mi')-to_date(start_time, 'hh24:mi'))*24)||':'|| round(((to_date(end_time, 'hh24:mi')-to_date(start_time, 'hh24:mi'))*24 - trunc((to_date(end_time, 'hh24:mi')-to_date(start_time, 'hh24:mi'))*24))*60) from tabname;示例:SQL> select trunc((to_date('08:30','hh24:mi')-to_date('07:50','hh24:mi'))*24)||':'|| 2 round(((to_date('08:30','hh24:mi')-to_date('07:50','hh24:mi'))*24 - 3 trunc((to_date('08:30','hh24:mi')-to_date('07:50','hh24:mi'))*24))*60) 4 from dual;TRUNC((TO_DATE('08:30','HH24:M--------------------------------------------------------------------------------0:40 select to_char(to_date(Start_time,'hh24:mi')-to_date(End_time,'hh24:mi')+to_date('00:00','hh24:mi'),'hh24:mi') from table SQL> select to_char(interval '8:20' hour to minute - interval '7:40' hour to minute,'hh24:mi') from dual;TO_CHAR(INTERVAL'8:20'HOURTOMI------------------------------+000000000 00:40:00.000000000SQL> select to_char(trunc(sysdate) + interval '8:20' hour to minute - interval '7:40' hour to minute,'hh24:mi') from dual;TO_CHAR(TRUNC(SYSDATE)+INTERVA------------------------------00:40 SQL> select * from d;A B----- -----08:30 07:5020:14 15:21SQL> select to_char(to_date(a,'hh24:mi')-to_date(b,'hh24:mi')+to_date('00:00','hh24:mi'),'hh24:mi') from d;TO_CH-----00:4004:53SQL> 尋兄的 select to_char(trunc(sysdate) + interval '8:20' hour to minute - interval '7:40' hour to minute,'hh24:mi') from dual;怎么換成我的欄位就出錯啊水清兄的也不錯 Oracle分区表,经常删除会造成磁盘碎片过多吗? RedHat 5下Oracle关机再开机就不能连接。 oracle imp问题? 请教各位大侠一个分组统计的问题 高手请入:java调用存储过程(答的好的话狂给分。。。) 批量更新数据库 oracle10g 安装到填写完SID后下一个界面开始89%就不动了,为什么,怎么办 怎麽顯示星期幾 安装oracle,字符集选缺省的,但不能输入中文!可修改否?如何修改? 小弟刚学数据库,有个问题请教各位 请问,如何将一个select语句查询得到的结果输出到一个外部文件呢. oracle UTL_FILE包如何实现追加文件内容
具體
round(((to_date(end_time, 'hh24:mi')-to_date(start_time, 'hh24:mi'))*24 - trunc((to_date(end_time, 'hh24:mi')-to_date(start_time, 'hh24:mi'))*24))*60)
from tabname;示例:
SQL> select trunc((to_date('08:30','hh24:mi')-to_date('07:50','hh24:mi'))*24)||':'||
2 round(((to_date('08:30','hh24:mi')-to_date('07:50','hh24:mi'))*24 -
3 trunc((to_date('08:30','hh24:mi')-to_date('07:50','hh24:mi'))*24))*60)
4 from dual;TRUNC((TO_DATE('08:30','HH24:M
--------------------------------------------------------------------------------
0:40
------------------------------
+000000000 00:40:00.000000000SQL> select to_char(trunc(sysdate) + interval '8:20' hour to minute - interval '7:40' hour to minute,'hh24:mi') from dual;TO_CHAR(TRUNC(SYSDATE)+INTERVA
------------------------------
00:40
----- -----
08:30 07:50
20:14 15:21SQL> select to_char(to_date(a,'hh24:mi')-to_date(b,'hh24:mi')+to_date('00:00','h
h24:mi'),'hh24:mi') from d;TO_CH
-----
00:40
04:53SQL>