Declare @i int,@t1 datetime , @t2 datetime; select @i = 0, @t1 = getdate(); while @i<1000000 begin declare @BeginDate datetime, @EndDate datetime -- 方法1,用datediff select @BeginDate = cast(datediff(day, 0, getdate()) as datetime) , @EndDate = dateadd(ms,-2,datediff(day,-1,getdate())) --select @BeginDate datetime, @EndDate set @i=@i+1 end select @t2 = getdate(); print convert(nvarchar(100), convert(float, @t2-@t1)) go Declare @i int,@t1 datetime , @t2 datetime; select @i = 0, @t1 = getdate(); while @i<1000000 begin declare @BeginDate datetime, @EndDate datetime -- 方法2,获取当前天数来计算 select @BeginDate = ROUND (CONVERT(float, getdate()),0,1) -- 获取日期部分 2008-05-01 00:00:00.000 , @EndDate = @BeginDate + .999999999 -- 计算日期最后一秒 2008-05-01 23:59:59.997 --select @BeginDate datetime, @EndDate set @i=@i+1 end select @t2 = getdate(); print convert(nvarchar(100), convert(float, @t2-@t1))
测试结果
7.8125e-005 7.24923e-005 第二种会比第一种方法快那么一点,呵呵.
其实计算为2天的范围更好 '2008-06-01 00:00:00.000' '2008-06-02 00:00:00.000'
比较时用 CreatTime >= @begin and CreateTime < @end