select * from 表 where 时间字段 between '2007-1-1' and '2007-6-6'
select * from 表 where 时间 BETWEEN '2007-1-1' and '2007-6-6'
select top 1000 id=identity(int ,1,1) into #a from sysobjects a ,sysobjects bselect dateadd(dd,id,'2007-1-1'),id from #a where dateadd(dd,id,'2007-1-1')<='2007-6-6'
set nocount on declare @t1 datetime declare @t2 datetime declare @i int set @t1='2007-1-1' set @t2='2007-6-6' declare @tbl table(t datetime) set @i=0 while @i<=datediff(day,@t1,@t2) begin insert into @tbl select dateadd(day,@i,@t1) set @i=@i+1 endselect * from @tbl
declare @dtBegin datetime declare @dtEnd datetime declare @t table(dt datetime)set datefirst 1 set @dtBegin='2007-1-1' set @dtEnd='2007-6-1'while @dtBegin<@dtEnd begin if datediff(day,@dtBegin,@dtEnd)<>0 begin insert @t select @dtBegin end set @dtBegin=@dtBegin+1 end select * from @t
declare @t table(id int identity(1, 1), dt datetime) declare @d datetimeselect @d = cast('2007-1-1' as datetime)while @d <= '2007-6-6' begin insert @t select @d set @d = dateadd(day, 1, @d) endselect dt from @t
select top 800 id=identity(int ,0,1) into #a from syscolumnsselect convert(varchar(10), dateadd(dd,id,'2007-1-1'),120),id from #a where dateadd(dd,id,'2007-1-1')<='2007-6-6' drop table #a
有本地和全局两种类型的临时表,二者在名称、可见性和可用性上均不相同。本地临时表的名称以单个数字符号(#)打头;它们仅对当前的用户连接是可见的;当用户从 Microsoft®SQLServer™ 2000 实例断开连接时被删除。全局临时表的名称以数学符号(##) 打头,创建后对任何用户都是可见的,当所有引用该表的用户从 SQL Server 断开连接时被删除。
step 1 -------------------------------------------------------- create table #date( riqi datetime ) ------------------- step 2 ------------------ declare @start_day datetime,@end_day datetime,@add_day datetime set @start_day = '2007-01-01' set @end_day ='2007-06-06'while @start_day <= @end_day begin insert into #date select @start_day select @start_day = dateadd(day,1,@start_day) end --------------------------------------------------- step 3 --------------------------------------------------- select * from #date
declare @table table(ddd datetime) declare @stardate datetime, @enddate datetime set @stardate ='2007-1-1' set @enddate ='2007-6-6' insert into @table select @stardate while @stardate < @enddate begin set @stardate= dateadd(day,1,@stardate) insert into @table select @stardate endselect * from @table
select top 1000 id=identity(int ,1,1) into #a from sysobjects a ,sysobjects bselect dateadd(dd,id,'2007-1-1'),id from #a where dateadd(dd,id,'2007-1-1')<='2007-6-6' drop table #a#a应该不会自动释放吧
'2007-1-1' and '2007-6-6'
declare @t1 datetime
declare @t2 datetime
declare @i int
set @t1='2007-1-1'
set @t2='2007-6-6'
declare @tbl table(t datetime)
set @i=0
while @i<=datediff(day,@t1,@t2)
begin
insert into @tbl select dateadd(day,@i,@t1)
set @i=@i+1
endselect * from @tbl
declare @dtEnd datetime
declare @t table(dt datetime)set datefirst 1
set @dtBegin='2007-1-1'
set @dtEnd='2007-6-1'while @dtBegin<@dtEnd
begin
if datediff(day,@dtBegin,@dtEnd)<>0
begin
insert @t select @dtBegin
end
set @dtBegin=@dtBegin+1
end
select * from @t
declare @d2 datetime
declare @s varchar(1000)
declare @i int
declare @f intselect
@s = '',
@f = 0,
@d1 = '2007-1-1',
@d2 = '2007-6-6,
@i = datediff(day,@d1,@d2)while @f <= @i
begin
select @s = @s + 'select '''
+ convert(varchar(10),dateadd(day,@f,@d1),120)
+ ''' union all '
select @f = @f + 1
endselect @s = substring(@s,1,len(@s)-10)exec(@s)
declare @d datetimeselect @d = cast('2007-1-1' as datetime)while @d <= '2007-6-6'
begin
insert @t select @d
set @d = dateadd(day, 1, @d)
endselect dt from @t
select top 800 id=identity(int ,0,1) into #a from syscolumnsselect convert(varchar(10), dateadd(dd,id,'2007-1-1'),120),id from #a where dateadd(dd,id,'2007-1-1')<='2007-6-6'
drop table #a
----------------------------------------------------------
临时表(#开头)存储在 tempdb 中,当不再使用时会自动删除。
有本地和全局两种类型的临时表,二者在名称、可见性和可用性上均不相同。本地临时表的名称以单个数字符号(#)打头;它们仅对当前的用户连接是可见的;当用户从 Microsoft®SQLServer™ 2000 实例断开连接时被删除。全局临时表的名称以数学符号(##) 打头,创建后对任何用户都是可见的,当所有引用该表的用户从 SQL Server 断开连接时被删除。
--------------------------------------------------------
create table #date(
riqi datetime
)
-------------------
step 2
------------------
declare @start_day datetime,@end_day datetime,@add_day datetime
set @start_day = '2007-01-01'
set @end_day ='2007-06-06'while @start_day <= @end_day
begin
insert into #date select @start_day
select @start_day = dateadd(day,1,@start_day)
end
---------------------------------------------------
step 3
---------------------------------------------------
select * from #date
declare @stardate datetime, @enddate datetime
set @stardate ='2007-1-1'
set @enddate ='2007-6-6'
insert into @table select @stardate
while @stardate < @enddate begin
set @stardate= dateadd(day,1,@stardate)
insert into @table select @stardate
endselect * from @table
drop table #a#a应该不会自动释放吧