时间加减

losinghappy 2012-01-10 02:29:28

表
id t1 t2 s
1 2011-11-02 00:01:02 2011-11-02 00:01:08 0.16
2 2011-11-02 00:01:07 2011-11-02 00:01:11 0.15
3 2011-11-02 00:01:15 2011-11-02 00:01:22 0.17
4 2011-11-02 00:01:22 2011-11-02 00:01:29 0.16
5 2011-11-02 00:01:56 2011-11-02 00:02:08 0.17

。。
。。
。。
计算得新表使新列t1=t1+0.4/s
新列t2=t2-0.6/s

...全文

109 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

勿勿 2012-01-10

打赏
举报

declare @表 table (id int,t1 datetime,t2 datetime,s numeric(3,2))

insert into @表

select 1,'2011-11-02 00:01:02','2011-11-02 00:01:08',0.16 union all

select 2,'2011-11-02 00:01:07','2011-11-02 00:01:11',0.15 union all

select 3,'2011-11-02 00:01:15','2011-11-02 00:01:22',0.17 union all

select 4,'2011-11-02 00:01:22','2011-11-02 00:01:29',0.16 union all

select 5,'2011-11-02 00:01:56','2011-11-02 00:02:08',0.17



select *,dateadd(SS,CEILING(0.4*s),t1)as 't1=t1+0.4/s',dateadd(SS,-CEILING(0.6*s),t2)as 't2=t2-0.6/s'from @表





id          t1                      t2                      s                                       t1=t1+0.4/s             t2=t2-0.6/s

----------- ----------------------- ----------------------- --------------------------------------- ----------------------- -----------------------

1           2011-11-02 00:01:02.000 2011-11-02 00:01:08.000 0.16                                    2011-11-02 00:01:03.000 2011-11-02 00:01:07.000

2           2011-11-02 00:01:07.000 2011-11-02 00:01:11.000 0.15                                    2011-11-02 00:01:08.000 2011-11-02 00:01:10.000

3           2011-11-02 00:01:15.000 2011-11-02 00:01:22.000 0.17                                    2011-11-02 00:01:16.000 2011-11-02 00:01:21.000

4           2011-11-02 00:01:22.000 2011-11-02 00:01:29.000 0.16                                    2011-11-02 00:01:23.000 2011-11-02 00:01:28.000

5           2011-11-02 00:01:56.000 2011-11-02 00:02:08.000 0.17                                    2011-11-02 00:01:57.000 2011-11-02 00:02:07.000



(5 行受影响)

勿勿 2012-01-10

打赏
举报

select *,dateadd(SS,0.4,t1)as 't1=t1+0.4/s',dateadd(SS,-0.6,t2)as 't2=t2-0.6/s'from tb

叶子 2012-01-10

打赏
举报



declare @表 table (id int,t1 datetime,t2 datetime,s numeric(3,2))

insert into @表

select 1,'2011-11-02 00:01:02','2011-11-02 00:01:08',0.16 union all

select 2,'2011-11-02 00:01:07','2011-11-02 00:01:11',0.15 union all

select 3,'2011-11-02 00:01:15','2011-11-02 00:01:22',0.17 union all

select 4,'2011-11-02 00:01:22','2011-11-02 00:01:29',0.16 union all

select 5,'2011-11-02 00:01:56','2011-11-02 00:02:08',0.17



--不知道是不是这个意思

select t1,t2,s,

newt1=DATEADD(s,CEILING(0.4*s),t1),

newt2=DATEADD(s,-CEILING(0.6*s),t1)

from @表

/*

t1                      t2                      s                                       newt1                   newt2

----------------------- ----------------------- --------------------------------------- ----------------------- -----------------------

2011-11-02 00:01:02.000 2011-11-02 00:01:08.000 0.16                                    2011-11-02 00:01:03.000 2011-11-02 00:01:01.000

2011-11-02 00:01:07.000 2011-11-02 00:01:11.000 0.15                                    2011-11-02 00:01:08.000 2011-11-02 00:01:06.000

2011-11-02 00:01:15.000 2011-11-02 00:01:22.000 0.17                                    2011-11-02 00:01:16.000 2011-11-02 00:01:14.000

2011-11-02 00:01:22.000 2011-11-02 00:01:29.000 0.16                                    2011-11-02 00:01:23.000 2011-11-02 00:01:21.000

2011-11-02 00:01:56.000 2011-11-02 00:02:08.000 0.17                                    2011-11-02 00:01:57.000 2011-11-02 00:01:55.000

*/