Sql-server – Count days in date range but only for a specific year within the date range

datesql server

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]

Best Answer

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

Related Solutions

Sql-server – How to calculate exact month (in days) difference b/w two dates

The problem with your problem (sorry, couldn't resist) is that you haven't properly defined what you expect a month to be. How many months are there between Dec 27 and Feb 28? How about Dec 28 and Feb 28? Dec 29? What about Dec 15 to Jan 13? Dec 15 to Jan 14?

As others have mentioned, DATEDIFF just measures the number of boundaries that have been passed. The following both return 1:

SELECT 
  DATEDIFF(YEAR,'20120101','20131231'), 
  DATEDIFF(YEAR,'20121231','20130101');

Even though you and I both know that only one day has passed in the first example, and almost two years have passed in the second.

If you want everything from before September 4th (since today is February 3rd), then:

DECLARE @d DATE = SYSDATETIME;

SELECT <cols> FROM dbo.tablename
  WHERE VisitDate < DATEADD(DAY, 1, DATEADD(MONTH, -5, @d));

-- the above evaluates to < '2013-09-04'

Mysql – Datediff() in MySQL

Aaron's answer is essentially right, but I would not recommend DATEDIFF in the WHERE clause because it could possibly impose a table scan. Instead, I suggest this

SELECT
   user.*,
   company.*.
   DATEDIFF(Company.CreatedOn,NOW()) NumDays
from dbname.company
inner join dbname.user
on user.company_id = company.company_id
WHERE company.CreatedOn < DATE(NOW() - INTERVAL 1 YEAR);

You could take further and refactor the query as follows:

SELECT
   U.*,
   C.*.
   DATEDIFF(C.CreatedOn,NOW()) NumDays
from
(select * from dbname.company
WHERE CreatedOn < DATE(NOW() - INTERVAL 1 YEAR)) C
inner join dbname.user U
on U.company_id = C.company_id;

Best Answer

Related Solutions

Sql-server – How to calculate exact month (in days) difference b/w two dates

Mysql – Datediff() in MySQL

Related Question