多列日期格式的字段 怎样取在某特定日期所在区间的首日期

Question

本帖最后由 xujiagang 于 2010-04-01 11:03:23 编辑

Answer 1

if not object_id('tb') is null
drop table tb
Go
Create table tb([t11] Datetime,[t12] Datetime,[t21] Datetime,[t22] Datetime,[t31] Datetime,[t32] Datetime,[t41] Datetime,[t42] Datetime)
Insert tb
select '2010-2-1','2010-2-6','2010-2-7','2010-2-13','2010-2-14','2010-2-20','2010-2-21','2010-2-28' union all
select '2010-3-1','2010-3-6','2010-3-7','2010-3-13','2010-3-14','2010-3-20','2010-3-21','2010-3-27' union all
select '2011-3-28','2010-4-3','2010-4-4','2010-4-10','2010-4-11','2010-4-23','2010-4-24','2010-4-30'
Go
select 区间,
      [t11]首日期
from
(
Select [t11],
       [t12],
       '[t11-t12]'区间
from tb
union all
Select [t21],
       [t22],
       '[t21-t22]'区间
from tb
union all
Select [t31],
       [t32],
       '[t31-t32]'区间
from tb
union all
Select [t41],
       [t42],
       '[t41-t42]'区间
from tb
)t
where '2010-3-16' between [t11]and [t12]
/*
区间        首日期
--------- -----------------------
[t31-t32] 2010-03-14 00:00:00.000(1 個資料列受到影響)
*/