请教两个日期相减的SQL写法

starorion

oracle的写法是这样的:

select trunc(to_number((a.endtime-a.starttime)*100000)) from log a

我在informix试了一下这样的写法:

select trunc((cast('to_char(a.endtime)' as integer) - cast('to_char(a.starttime)' as integer))*100000)) from log a

但发现是错误的.现请教各位高手 正解是什么样的.


说明:informix中a.starttime 和 a.endtime  为datetime型

吸学蚊子

我写的一个
select
extend(to_date(copyrightdeadline,"%iy%m%d"),year to day)
-extend(to_date(auditdate,"%iy%m%d"),year to day)
from crbt_ring
where codeno='022650'

liaosnet

做个测试..........
> create table t1
>   ( id char(10),
>    stime datetime year to day,
>     etime datetime year to second
>   );

> insert into t1 values("01","2008-04-20","2008-04-21 00:00:00");

> select * from t1;
id         stime      etime               
01         2008-04-20 2008-04-21 00:00:00

>select etime - stime from t1;
(expression)      
        1 00:00:00
1 row(s) retrieved.
> select extend(etime,year to day) - stime from t1;
(expression)
        1
1 row(s) retrieved.


> update t1 set etime="2008-04-21 01:10:59" where id="01";

1 row(s) updated.

> select * from t1;
id         stime      etime               
01         2008-04-20 2008-04-21 01:10:59

1 row(s) retrieved.

> select etime - stime from t1;
(expression)      
        1 01:10:59

1 row(s) retrieved.
> select extend(etime,year to day) - stime from t1;
(expression)
        1

1 row(s) retrieved.

juliey

A easy function:

select date(etime) - stime from t1;