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

starorion 发表于 2007-09-06 17:47

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型

吸学蚊子 发表于 2008-04-21 15:53

我写的一个
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 发表于 2008-04-21 16:31

回复 #1 starorion 的帖子

做个测试..........
> 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 发表于 2008-04-24 00:08

A easy function:

select date(etime) - stime from t1;

页: [1]

Chinaunix's Archiver

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

回复 #1 starorion 的帖子