Count number of midnights between two dates

• NineIron

  SSChampion

  Points: 12539

  More actions

  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')
  

• tripleAxe

  SSCertifiable

  Points: 5707

  More actions

  April 16, 2019 at 2:01 pm

  #3409882

  Does DATEDIFF not work for you?

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

• NineIron

  SSChampion

  Points: 12539

  More actions

  April 16, 2019 at 2:36 pm

  #3410378

  Pardon my ignorance

• Michael L John

  One Orange Chip

  Points: 27077

  More actions

  April 16, 2019 at 3:20 pm

  #3410707

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

  More actions

  April 16, 2019 at 3:27 pm

  #3410797

  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.