# Count number of midnights between two dates

• NineIron

SSChampion

Points: 12525

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')`
• tripleAxe

SSCertifiable

Points: 5605

Does DATEDIFF not work for you?

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

• NineIron

SSChampion

Points: 12525

Pardon my ignorance

• Michael L John

One Orange Chip

Points: 25950

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: 12525

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.

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