Find out the number of Days and nights using sql query

Question

I would like to find out the number of days and nights in a given date range in sql. Can anyone help me? Thanks in advance.

Answer 1

Try something like this:

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

Result:

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