Post reply

How to determine if there are two midnights between two dates

  • June 26, 2018 at 6:02 am

    #352278

    I need to determine if there are two midnights between the OrderDateTime and the AdmitToObs dates. Any thoughts?

    create table #T

    (

    ID varchar(3),

    OrderDateTime datetime,

    AdmitToObs datetime

    )

    insert into #T(ID, OrderDateTime, AdmitToObs) values('001','2018-06-15 15:52','2018-06-15 08:52')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('002','2018-06-01 11:13','2018-05-31 00:26')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('003','2018-06-01 10:15','2018-05-31 20:44')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('004','2018-06-01 08:34','2018-06-01 06:47')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('005','2018-06-04 08:21','2018-06-01 16:47')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('006','2018-06-03 10:00','2018-06-02 10:16')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('007','2018-06-03 12:10','2018-06-02 16:14')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('008','2018-06-04 11:39','2018-06-03 15:06')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('009','2018-06-04 08:17','2018-06-03 17:51')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('010','2018-06-04 10:31','2018-06-03 18:44')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('011','2018-06-05 10:56','2018-06-04 01:34')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('012','2018-06-05 11:25','2018-06-04 22:53')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('013','2018-06-05 18:19','2018-06-04 21:53')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('014','2018-06-07 14:18','2018-06-07 14:24')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('015','2018-06-06 16:09','2018-06-05 20:00')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('016','2018-06-06 17:31','2018-06-05 20:56')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('017','2018-06-06 14:11','2018-06-06 00:18')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('018','2018-06-08 17:14','2018-06-07 04:55')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('019','2018-06-07 15:02','2018-06-07 06:00')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('020','2018-06-17 15:26','2018-06-15 12:45')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('021','2018-06-08 14:52','2018-06-08 02:22')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('022','2018-06-10 08:59','2018-06-08 20:21')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('023','2018-06-11 16:35','2018-06-09 12:20')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('024','2018-06-09 18:52','2018-06-09 17:00')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('025','2018-06-10 09:01','2018-06-09 20:22')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('026','2018-06-10 11:02','2018-06-10 00:06')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('027','2018-06-11 11:56','2018-06-11 02:39')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('028','2018-06-11 12:45','2018-06-11 10:24')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('029','2018-06-12 09:56','2018-06-11 16:32')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('030','2018-06-12 09:13','2018-06-11 23:14')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('031','2018-06-12 09:46','2018-06-11 23:01')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('032','2018-06-11 22:43','2018-06-11 19:38')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('033','2018-06-13 15:31','2018-06-12 12:58')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('034','2018-06-13 13:06','2018-06-12 15:13')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('035','2018-06-13 08:51','2018-06-12 16:11')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('036','2018-06-13 12:06','2018-06-12 21:59')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('037','2018-06-14 10:37','2018-06-13 21:46')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('038','2018-06-15 15:06','2018-06-14 15:56')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('039','2018-06-15 15:07','2018-06-14 17:46')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('040','2018-06-15 09:06','2018-06-14 23:54')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('041','2018-06-15 10:34','2018-06-15 00:11')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('042','2018-06-17 08:23','2018-06-15 20:16')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('043','2018-06-17 15:40','2018-06-15 22:59')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('044','2018-06-16 13:14','2018-06-16 04:45')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('045','2018-06-17 13:34','2018-06-16 16:32')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('046','2018-06-18 10:17','2018-06-17 04:36')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('047','2018-06-18 09:19','2018-06-17 22:53')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('048','2018-06-18 14:10','2018-06-18 04:42')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('049','2018-06-19 11:22','2018-06-18 20:22')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('050','2018-06-19 08:42','2018-06-18 23:14')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('051','2018-06-19 09:12','2018-06-19 00:32')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('052','2018-06-20 10:45','2018-06-19 14:01')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('053','2018-06-21 09:30','2018-06-21 02:39')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('054','2018-06-22 14:54','2018-06-21 15:07')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('055','2018-06-22 11:42','2018-06-21 20:31')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('056','2018-06-22 08:32','2018-06-22 07:39')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('057','2018-06-23 14:40','2018-06-25 09:42')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('058','2018-06-23 03:37','2018-06-23 02:25')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('059','2018-06-25 14:19','2018-06-24 15:03')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('060','2018-06-01 09:03','2018-06-07 13:34')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('061','2018-06-02 11:32','2018-06-01 20:57')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('062','2018-06-04 12:25','2018-06-02 18:40')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('063','2018-06-03 16:28','2018-06-07 13:27')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('064','2018-06-04 12:26','2018-06-02 20:12')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('065','2018-06-04 11:14','2018-06-02 21:54')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('066','2018-06-04 10:35','2018-06-03 01:26')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('067','2018-06-03 09:19','2018-06-03 01:21')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('068','2018-06-04 08:56','2018-06-03 14:17')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('069','2018-06-04 13:02','2018-06-03 19:51')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('070','2018-06-04 14:23','2018-06-03 21:10')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('071','2018-06-04 15:54','2018-06-04 15:14')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('072','2018-06-05 14:53','2018-06-04 18:55')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('073','2018-06-05 13:01','2018-06-04 22:57')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('074','2018-06-06 08:58','2018-06-06 02:57')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('075','2018-06-06 15:08','2018-06-06 15:13')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('076','2018-06-07 15:51','2018-06-06 15:21')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('077','2018-06-08 15:12','2018-06-07 08:06')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('078','2018-06-08 15:07','2018-06-08 00:09')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('079','2018-06-10 13:42','2018-06-10 00:49')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('080','2018-06-11 10:04','2018-06-10 11:30')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('081','2018-06-11 09:35','2018-06-10 21:56')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('082','2018-06-12 09:09','2018-06-12 09:48')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('083','2018-06-12 09:58','2018-06-12 02:05')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('084','2018-06-12 09:57','2018-06-12 03:37')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('085','2018-06-13 08:54','2018-06-12 11:46')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('086','2018-06-13 08:54','2018-06-12 18:21')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('087','2018-06-13 10:13','2018-06-12 22:54')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('088','2018-06-13 14:22','2018-06-13 01:42')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('089','2018-06-14 10:16','2018-06-13 14:24')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('090','2018-06-14 11:46','2018-06-13 17:05')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('091','2018-06-14 10:39','2018-06-13 18:59')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('092','2018-06-14 04:46','2018-06-14 08:25')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('093','2018-06-14 10:43','2018-06-13 21:06')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('094','2018-06-14 08:22','2018-06-14 01:37')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('095','2018-06-15 11:05','2018-06-14 15:53')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('096','2018-06-16 10:30','2018-06-15 19:28')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('097','2018-06-16 12:27','2018-06-16 02:01')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('098','2018-06-16 12:36','2018-06-15 23:41')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('099','2018-06-16 08:30','2018-06-16 00:24')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('100','2018-06-18 10:57','2018-06-16 02:42')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('101','2018-06-17 10:23','2018-06-18 12:09')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('102','2018-06-17 11:56','2018-06-16 22:58')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('103','2018-06-17 04:36','2018-06-17 00:10')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('104','2018-06-18 10:17','2018-06-16 23:58')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('105','2018-06-20 14:49','2018-06-20 15:14')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('106','2018-06-21 13:00','2018-06-20 07:38')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('107','2018-06-22 17:04','2018-06-25 09:11')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('108','2018-06-25 09:54','2018-06-22 15:57')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('109','2018-06-23 14:51','2018-06-22 20:27')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('110','2018-06-23 14:50','2018-06-22 22:21')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('111','2018-06-23 12:31','2018-06-22 22:27')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('113','2018-06-23 15:53','2018-06-23 00:05')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('114','2018-06-23 19:28','2018-06-23 06:24')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('115','2018-06-24 08:05','2018-06-23 12:23')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('116','2018-06-24 18:21','2018-06-23 10:06')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('117','2018-06-24 13:14','2018-06-23 21:06')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('118','2018-06-24 10:03','2018-06-23 20:30')

    insert into #T(ID, OrderDateTime, AdmitToObs) values('119','2018-06-25 09:09','2018-06-25 03:20')

  • June 26, 2018 at 6:35 am

    #1995270

    What have you tried so far? Have you looked at DATEDIFF?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • June 26, 2018 at 6:42 am

    #1995275

    I've tried doing datediff and using 48 hours as a cutoff but, I dont' think that will suffice.

  • June 26, 2018 at 10:45 am

    #1995327

    Just plain DATEDIFF(DAY, ...) will do that.  By definition, DATEDIFF counts the number of times the midnight boundary is reached/crossed between two dates/datetimes.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • June 26, 2018 at 10:49 am

    #1995329

    Thanx Scott. I didn't think it would be this simple.

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

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