如何来比较这样的时间呢

[zywhao]

数据表中有个这样几个字段

ID: 唯一标识
ETA: 到达日期(月-日 时:分)
LastUpdate: 最后更新日期

数据如下:

ID ETA LastUpdate
1 5-27 9:0 2010-05-25 18:49:09
2 12-30 23:0 2010-12-28 18:19:09
3 1-2 13:0 2010-12-24 0:5:27
4 1-5 4:30 2010-12-27 0:23:27

lastUPdate的时间肯定会比ETA时间要早.

我需要查询的条件是 ETA时间小于 2011年1月5日5时之前并且大于当前日期减一天(例如当前日期为2010-12-27)的所有数据

[这里面有个重要的问题是: ID=4的, 最后更新日期为2010-12-27日,那EAT时间肯定为2011-1-5 4:30 不是2010-1-5 4:30]

这样怎么来写SQL语句呢

[快溜]

哎呦妈呀。

[jxqn_liu]

年份都木有，怎么确定年份
这里写了个例子转换的，看看，你那个ETA得先转换成日期的才能进行比较

declare @t table (ID int, ETA varchar(20), LastUpdate datetime)
insert into @t
select 1, '5-27 9:0', '2010-05-25 18:49:09' union all
select 2, '12-30 23:0', '2010-12-28 18:19:09' union all
select 3, '1-2 13:0', '2010-12-24 0:5:27' union all
select 4, '1-5 4:30', '2010-12-27 0:23:27' union all
select 5, '5-27 9:0', '2009-05-25 18:49:09' union all
select 6, '12-30 23:0', '2011-12-28 18:19:09' union all
select 7, '1-2 13:0', '2012-12-24 0:5:27' 


select id, ETA, convert(datetime, cast(Year(LastUpdate)+1 as varchar)+'-'+ETA, 121) ETADate, LastUpdate from @t

[--小F--]

请问你的时间字段中没有年份么?

[zywhao]

预到时间没有年份.. 信息来自于设备

[zywhao]

主要是 跨年时不太好玩..

[jxqn_liu]

declare @t table (ID int, ETA varchar(20), LastUpdate datetime)
insert into @t
select 1, '5-27 9:0', '2010-05-25 18:49:09' union all
select 2, '12-30 23:0', '2010-12-28 18:19:09' union all
select 3, '1-2 13:0', '2010-12-24 0:5:27' union all
select 4, '1-5 4:30', '2010-12-27 0:23:27' union all
select 5, '5-27 9:0', '2009-05-25 18:49:09' union all
select 6, '12-30 23:0', '2011-12-28 18:19:09' union all
select 7, '1-2 13:0', '2012-12-24 0:5:27' 


select id, ETA, LastUpdate
      ,case when Month(LastUpdate)=12 then convert(datetime, cast(Year(LastUpdate)+1 as varchar)+'-'+ETA, 121) 
            else convert(datetime, cast(Year(LastUpdate) as varchar)+'-'+ETA, 121) end  ETADate
 from @t


/*
--效果
id      ETA             LastUpdate              ETADate
1	5-27 9:0	2010-05-25 18:49:09.000	2010-05-27 09:00:00.000
2	12-30 23:0	2010-12-28 18:19:09.000	2011-12-30 23:00:00.000
3	1-2 13:0	2010-12-24 00:05:27.000	2011-01-02 13:00:00.000
4	1-5 4:30	2010-12-27 00:23:27.000	2011-01-05 04:30:00.000
5	5-27 9:0	2009-05-25 18:49:09.000	2009-05-27 09:00:00.000
6	12-30 23:0	2011-12-28 18:19:09.000	2012-12-30 23:00:00.000
7	1-2 13:0	2012-12-24 00:05:27.000	2013-01-02 13:00:00.000
*/

这样应该可以了

[jxqn_liu]

上面跨年么怎么处理好，看这个的吧

select id, ETA, LastUpdate
      ,case when Month(LastUpdate)=12 
                 and Month(LastUpdate) >= cast(substring(ETA, 1, charindex('-',ETA) - 1) as int) 
                 and Day(LastUpdate) >= cast(substring(ETA, charindex('-',ETA) + 1, charindex(' ',ETA)-charindex('-',ETA)) as int) 
then convert(datetime, cast(Year(LastUpdate)+1 as varchar)+'-'+ETA, 121) 
            else convert(datetime, cast(Year(LastUpdate) as varchar)+'-'+ETA, 121) end  ETADate
 from @t

[zywhao]

我来试试,, 谢谢兄弟这么热心的解答