0

I have several date spans in a SQL Server table.

I am trying to count the number of days in the date span for a given year in the date span.

For all my examples I am trying count the numbers of days in 2019 that the date span covers.

I have the below code which calculates the total days in the date range but is there a way I can just get a count of the days within that date range for 2019 only?

The first date span below covers all of 2019 so I'm hopping to return a number around 365.

The second date span covers only 30 days in 2019 so I would like to have the query return 30.

SELECT DATEDIFF(DAY,CAST(CAST(20160609 AS VARCHAR(30)) AS DATE), cast (CAST(20191231 AS VARCHAR(30)) AS DATE)) AS [Days] 

SELECT DATEDIFF(DAY,CAST(CAST(20160609 AS VARCHAR(30)) AS DATE), cast (CAST(20190130 AS VARCHAR(30)) AS DATE)) AS [Days] 
2

Assuming your table has a StartDate and EndDate field, you can use a CASE statement to calculate the days difference between the end date and either 01/01/2019 or the start date of the range.

SELECT ID, StartDate, EndDate, 
  (CASE
    WHEN StartDate < '2019-01-01' THEN DATEDIFF(DAY, '2019-01-01', EndDate)
    ELSE DATEDIFF(DAY, StartDate, EndDate)
  END)+1 AS DaysInRange
FROM Dates

db<>fiddle

1
  • I have startdate and endate fields in the table. this worked perfectly. thank you! – Juan Velez Jun 18 '20 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.