April 16, 2019 at 1:50 pm
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')
April 16, 2019 at 2:01 pm
Does DATEDIFF not work for you?
select *, DATEDIFF(DAY, StartDate, EndDate) from #T;
April 16, 2019 at 2:36 pm
Pardon my ignorance
April 16, 2019 at 3:20 pm
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/
April 16, 2019 at 3:27 pm
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply