Count days in date range but only for a specific year within the date range 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]
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 &lt; '2019-01-01' THEN DATEDIFF(DAY, '2019-01-01', EndDate)
    ELSE DATEDIFF(DAY, StartDate, EndDate)
  END)+1 AS DaysInRange
FROM Dates

db&lt;&gt;fiddle