使用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

Answer 2

请参阅Anandphadke的评论，这是代码：）

DECLARE @StartDate DATETIME = '2012-01-01'
DECLARE @EndDate DATETIME = '2012-02-01' 
SELECT DATEDIFF(DAY, @StartDate, @EndDate) AS [Days],  DATEDIFF(DAY, @StartDate, @EndDate) AS [Nights ]

Answer 3

您是否尝试过Datediff

晚上

select DATEDIFF (d, getdate()-1,getdate())

天

select DATEDIFF (d, getdate()-1,getdate()) - 1