如何得到2个日期(smalldateTime)间隔天数??神仙来帮我啊

Question

datediff( day,'2008-11-01 10:37:00 ','2008-11-01 11:39:00 ')

Answer 1

declare @dt1 datetime,@dt2 datetime,@dt3 datetime,@dt4 datetimeselect @dt1='2008-11-01 10:37:00 ',@dt2=' 2008-11-01 11:39:00 ',
       @dt3='2008-11-02 2:00:00 ',@dt4='2008-11-02 11:23:00'
select datediff(day,@dt1,@dt2)+1
select datediff(day,@dt1,@dt3)+1
select datediff(day,@dt1,@dt4)+1结果依次为：
1
2
3

Answer 2

哦，不好意思，又写错了。应该判断到小时上面declare @dt1 datetime,@dt2 datetime,@dt3 datetime,@dt4 datetimeselect @dt1='2008-11-01 10:37:00 ',@dt2=' 2008-11-01 11:39:00 ',
       @dt3='2008-11-02 2:00:00 ',@dt4='2008-11-02 11:23:00'
if  datediff(hh,@dt1,@dt2)<24
    print '一天'
else
   print '两天'
if  datediff(hh,@dt1,@dt3)<24
    print '一天'
else
   print '两天'
if datediff(hh,@dt1,@dt4)<24
    print '一天'
else
   print '两天'

Answer 3

我想肯定用datediff()函数
select datediff(day,date1,date2)

Answer 4

datediff( h,'2008-11-01 10:37:00 ','2008-11-01 11:39:00 ')/24

Answer 5

declare @dt1 datetime,@dt2 datetime,@dt3 datetime,@dt4 datetime select @dt1='2008-11-01 10:37:00 ',@dt2=' 2008-11-01 11:39:00 ', 
      @dt3='2008-11-02 2:00:00 ',@dt4='2008-11-02 11:23:00' --一天是43200秒
select ceiling( CAST(datediff( s, @dt1, @dt2 ) as decimal(18,2)) / 43200 ),
       ceiling( CAST(datediff( s, @dt1, @dt3 ) as decimal(18,2)) / 43200 ),
       ceiling( CAST(datediff( s, @dt1, @dt4 ) as decimal(18,2)) / 43200 )
--结果
/*
                                                                                 
-------------------------- -------------------------- -------------------------- 
1                          2                          3
*/使用秒来判断是比较准确的。

Answer 6

......select 24 * 60 * 60/**
86400
**/

Answer 7

按照楼主的需求，精确到分，实际上
2008-11-01 10:37:00
2008-11-02 11:39:00
是应该得到2天但是：
select datediff(hh,'2008-11-01 10:37:00 ','2008-11-02 11:39:00 ')/24/**
1
**/我想我的理解应该没有错吧。

Answer 8

嘿嘿，select datediff( mi,'2008-11-01 10:37:00 ','2008-11-02 11:39:00 ')/(24*60)

Answer 9

select ceiling(datediff(hh,'2008-11-01 10:37:00 ','2008-11-02 11:39:00 ')*1./24)

Answer 10

declare @dt1 datetime,@dt2 datetime,@dt3 datetime,@dt4 datetime select @dt1='2008-11-01 10:37:00 ',@dt2=' 2008-11-01 11:39:00 ', 
      @dt3='2008-11-02 2:00:00 ',@dt4='2008-11-02 11:23:00' 
select ceiling( CAST(datediff( mi, @dt1, @dt2 ) as decimal(18,2)) / (24 * 60) ),
       ceiling( CAST(datediff( mi, @dt1, @dt3 ) as decimal(18,2)) / (24 * 60) ),
       ceiling( CAST(datediff( mi, @dt1, @dt4 ) as decimal(18,2)) / (24 * 60) )
--结果
/*
                                                                                                
------------------------------- ------------------------------- ------------------------------- 
1                               1                               2*/
出笑话了

Answer 11

declare @T table(Date datetime)
insert @T select '2008-11-01 11:39:00'
insert @T select '2008-11-02 2:00:00'
insert @T select '2008-11-02 11:23:00'declare @beginTime datetime
set @beginTime='2008-11-01 10:37:00'select 
[Day]=case when Dateadd(d,datediff(d,@beginTime,Date),@beginTime)<=Date then datediff(d,@beginTime,Date)+1 else datediff(d,@beginTime,Date) end
from 
@T
(1 行受影响)
Day
-----------
1
1
2(3 行受影响)

Answer 12

declare @dt1 datetime,@dt2 datetime
select @dt1='2008-11-01 10:37:00 ',@dt2=' 2008-11-04 11:39:00 '
select datediff(hh,@dt1,@dt2)/24+1 

Answer 13

declare @dt1 datetime,@dt2 datetime,@dt3 datetime,@dt4 datetime
select @dt1='2008-11-01 10:37:00 ',@dt2=' 2008-11-01 10:39:00 ',@dt3='2008-11-02 2:00:00 ',@dt4=' 2008-11-02 11:23:00 '
select datediff(hh,@dt1,@dt2)/24+1 
select datediff(hh,@dt1,@dt3)/24+1 
select datediff(hh,@dt1,@dt4)/24+1 呵呵，用它测试吧，我刚测试完的，完全满足，其实只要计算到小时就可以了

Answer 14

非常感谢大家 我最后采用的方法是select datediff( mi,'2008-11-01 10:37:00 ','2008-11-02 11:39:00 ')/(24*60)+1
求出总的间隔分钟 然后求商得天数再+1