Count number of midnights between two dates

Question

Post reply

Count number of midnights between two dates

NineIron

SSChampion

Points: 12529

April 16, 2019 at 1:50 pm

#3409313

I need to count the number of midnights between two dates. Any thoughts?

create table #T
(
StartDate datetime,
EndDate datetime
)

insert into #T(StartDate,EndDate) values('2019-04-15 18:16','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 20:42','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-14 08:57','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 15:18','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 22:46','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 23:34','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 17:53','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 06:23','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 02:07','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 12:41','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-16 03:49','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-15 20:30','2019-04-16 09:38')
insert into #T(StartDate,EndDate) values('2019-04-12 14:34','2019-04-16 09:38')

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

tripleAxe SSCertifiable Points: 5706 More actions · Answer 1

Does DATEDIFF not work for you?

select *, DATEDIFF(DAY, StartDate, EndDate) from #T;

NineIron SSChampion Points: 12529 More actions · Answer 2

NineIron

SSChampion

Points: 12529

April 16, 2019 at 2:36 pm

#3410378

Pardon my ignorance

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

tripleAxe wrote:

Does DATEDIFF not work for you? select *, DATEDIFF(DAY, StartDate, EndDate) from #T;

Actually, it may not.

If start date is at midnight, such as '2019-04-14 00:00', it would not count that.

insert into #T(Ordinal, StartDate,EndDate) values(3, '2019-04-14 00:00','2019-04-16 23:00')

Using your formula against the above data, it will return 2. Depending upon what the requirements may be, this may not work.

OP, using the above, is this 2 midnights, or 3 midnights????

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

NineIron SSChampion Points: 12529 More actions · Answer 4

Thanx for your input. This is a Medicare 2 midnight rule report that I'm creating. I knew there would be an issue with someone being admitted at midnight but, the chances are very slim. However, if you have a more accurate answer, I'll take it.