select * from 表 where convert(varchar(10),date,120) between '2007-01-02'and ''2007-01-05' 或加小时段 select * from 表 where date between '2007-01-02 00:00:00'and ''2007-01-05 23:59:59'
他是要没有的日期也要列出,roy_88(论坛新星_燃烧你的激情!!) 没看清。。
create table T(id int, value varchar(10), [date] datetime) insert T select 1, 'aa', '2007-01-02' union all select 2, 'bb', '2007-01-01' union all select 3, 'cc', '2007-01-04'select isnull(B.value, '0') as value, A.* from ( select [Date]='2007-01-02' union all select '2007-01-03' union all select '2007-01-04' union all select '2007-01-05' ) as A left join T as B on A.[Date]=B.[Date] --result value Date ---------- ---------- aa 2007-01-02 0 2007-01-03 cc 2007-01-04 0 2007-01-05(4 row(s) affected)
create table ta(id int, value varchar(5), date datetime) insert ta select 1, 'aa' , '2007-01-02' union all select 2, 'bb' , '2007-01-01' union all select 3, 'cc' , '2007-01-04'生成一个时间段列: declare @ta table(t_date datetime) declare @i datetime set @i='2007-01-01 00:00:00' while @i!>'2007-1-31 00:00:00' begin insert @ta select convert(varchar(10),@i,120) select @i=@i+1 endselect 时间=convert(varchar(10),t_date,120),value=isnull(value,0) from @ta a , ta where convert(varchar(10),t_date,120)*=convert(varchar(10),date,120) and convert(varchar(10),t_date,120) between '2007-01-02'and '2007-01-05'时间 value ---------- ----- 2007-01-02 aa 2007-01-03 0 2007-01-04 cc 2007-01-05 0(所影响的行数为 4 行)
可以在表变量定义就行了 set @i='2007-01-01 00:00:00'--开始时间 while @i!>'2007-1-31 00:00:00'--结束时间用getdate()系统时间
declare @maxdate datatime set @i=(select min(date) from table) --开始时间 set @maxdate=(select max(date) from table) --表内的最大时间while @i!>@maxdate
或加小时段
select * from 表 where date between '2007-01-02 00:00:00'and ''2007-01-05 23:59:59'
create table T(id int, value varchar(10), [date] datetime)
insert T select 1, 'aa', '2007-01-02'
union all select 2, 'bb', '2007-01-01'
union all select 3, 'cc', '2007-01-04'select isnull(B.value, '0') as value, A.* from
(
select [Date]='2007-01-02'
union all select '2007-01-03'
union all select '2007-01-04'
union all select '2007-01-05'
) as A
left join T as B on A.[Date]=B.[Date] --result
value Date
---------- ----------
aa 2007-01-02
0 2007-01-03
cc 2007-01-04
0 2007-01-05(4 row(s) affected)
insert ta
select 1, 'aa' , '2007-01-02'
union all select 2, 'bb' , '2007-01-01'
union all select 3, 'cc' , '2007-01-04'生成一个时间段列:
declare @ta table(t_date datetime)
declare @i datetime
set @i='2007-01-01 00:00:00'
while @i!>'2007-1-31 00:00:00'
begin
insert @ta select convert(varchar(10),@i,120)
select @i=@i+1
endselect 时间=convert(varchar(10),t_date,120),value=isnull(value,0) from @ta a , ta
where convert(varchar(10),t_date,120)*=convert(varchar(10),date,120)
and convert(varchar(10),t_date,120) between '2007-01-02'and '2007-01-05'时间 value
---------- -----
2007-01-02 aa
2007-01-03 0
2007-01-04 cc
2007-01-05 0(所影响的行数为 4 行)
set @i='2007-01-01 00:00:00'--开始时间
while @i!>'2007-1-31 00:00:00'--结束时间用getdate()系统时间
set @i=(select min(date) from table) --开始时间
set @maxdate=(select max(date) from table) --表内的最大时间while @i!>@maxdate