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)

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