sql - 使用sql查询找出日夜数

Question

我想在sql中找出给定日期范围内的日数和夜数。谁能帮我？提前致谢。

Answer 1

尝试这样的事情:

declare @t table(datefrom datetime, dateto datetime)
insert @t values('2012-01-01 11:30', '2012-01-01 12:30')
insert @t values('2012-01-01 11:30', '2012-01-02 00:30')
insert @t values('2012-01-01 12:30', '2012-01-02 13:00')
insert @t values('2012-01-01 12:30', '2012-01-02 00:30')
insert @t values('2012-01-01 00:00', '2012-01-03 00:00')

select datefrom, dateto,
datediff(day, datefrom - .5,dateadd(minute, -1, dateto)) nights,
datediff(day, datefrom, dateadd(minute, -1, dateto)+.5) days
from @t t

结果:

datefrom         dateto            nights days
2012-01-01 11:30 2012-01-01 12:30  1      1
2012-01-01 11:30 2012-01-02 00:30  2      1
2012-01-01 12:30 2012-01-02 13:00  1      2
2012-01-01 12:30 2012-01-02 00:30  1      1
2012-01-01 00:00 2012-01-03 00:00  2      2