CODEWITHSUNDEEP
sql-server
theoryTim
theoryTim

Reputation: 13

SQL Server : calculating days elapsed

I need to get the number of elapsed days between any two dates with respect to the current date. IE:

mm/dd/yyyy

Current day = 07/10/2015  
07/08/2013 ...  07/11/2013 - 4 days elapsed

Current day = 07/10/2015  
07/08/2015 ... 07/11/2015 - 2 days have elapsed

I've tried several combinations using DATEDIFF with day as the date part, however, I can't seem to get a clean way to get the days elapsed when the date could be past or present.

EDIT

I know the start date and the end date of a certain business process. They could be this year, last year, two years ago and so on. I need a way via SQL Server functions to figure out the days total elapsed. If it's not the current year, obviously the entire span/range would have elapsed. If it's the current year, perhaps the entire span/range hasn't elapsed and it needs to say how many days are "into the process" based on the respected start time, end time and current time.

Hopefully this makes more sense?

Please help.

I used @Sean Lange, with a small tweak: DATEDIFF(DAY, @StartDate, case when @EndDate < GETDATE() then @EndDate + 1 else GETDATE() end)
Thanks all.

Upvotes: 1

Views: 3042

Answers (2)

Stan
Stan

Reputation: 983

How about this:

Given:

CREATE TABLE dbo.test ( ChildID INT Identity,
                            Start   DateTime
                    ,       Finish  DateTime
                    )

and your test data:

insert into dbo.test (start,finish) values('07/08/2013','07/11/2013')
insert into dbo.test (start,finish) values('07/08/2015','07/11/2015')

then

select start,finish
 , DATEDIFF(DAY, start, CASE WHEN GETDATE() BETWEEN start and finish 
             THEN GETDATE() - 1  ELSE finish END) + 1 as elapsed
 from dbo.test

gives the result from your example.

You might have to tweak if there are other adjustments for how the current date fits between the range.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

This is pretty similar to the answer provided by Stan but here is my take on this.

with Something as
(
    select CAST('2013-07-08' as datetime) as StartDate
        , CAST('2013-07-11' as datetime) as EndDate
    union all
    select '2015-07-08', '2015-07-11'
)

select *
    , DATEDIFF(DAY, StartDate, case when EndDate < GETDATE() then EndDate else GETDATE() end)
from Something

Upvotes: 2

Related Questions