Calculate how many month 'January' have occurred between two dates in SQL

user_3284707 0
Viewed 388

I need to get how many January's have occurred between two dates in TSQL.

I.e. If any day in January in a particular year has been included then this counts as 1 per year rather than multiple times per month.

As an example, the following would give the result = 1

declare @date1 datetime = '2018-09-01 00:00:00.000';
declare @date2 datetime = '2019-02-01 00:00:00.000';

As an example, the following would give the result = 1, as there has been 1 day into January

declare @date1 datetime = '2018-09-01 00:00:00.000';
declare @date2 datetime = '2019-01-01 00:00:00.000';

As an example, the following would give the result = 0 as there has been no days in January

declare @date1 datetime = '2018-09-01 00:00:00.000';
declare @date2 datetime = '2018-11-01 00:00:00.000';

As an example, the following would give the result = 2 as there has been two occurrences of January

declare @date1 datetime = '2017-09-01 00:00:00.000';
declare @date2 datetime = '2019-11-01 00:00:00.000';
tsql sql-server
2 Answers

Try using DATEDIFF() and DATEPART().

You can test to see if your start and end dates are outside a January date and then add -1...

DECLARE @startdate AS DATETIME = '2015-02-01'
DECLARE @enddate   AS DATETIME = '2019-06-01'

SELECT
    DATEDIFF(yy, @startdate, @enddate) 
    + 1 
    + CASE 
        WHEN DATEPART(m, @startdate) > 1 AND 
           DATEPART(m, @enddate) > 1 
        THEN -1
        ELSE  0
    END

I'm not sure how quick this would be over large datasets, however. If you are going to compare lots of data with a particular date/time set, then I would recommend testing Squirrel's answer.

To modify this for a different month, simply change the CASE clause like so...

+ CASE 
    WHEN DATEPART(m, @startdate) < 2 AND DATEPART(m, @startdate) > 2 AND 
         DATEPART(m, @enddate)   < 2 AND DATEPART(m, @enddate)   > 2

The 2 in the CASE statement is the month, so just change this to the appropriate month number.

If you'd like to alter this for any month, then include a parameter for the month number you're looking for...

+ CASE 
    WHEN DATEPART(m, @startdate) < @month_number AND 
         DATEPART(m, @startdate) > @month_number AND 
         DATEPART(m, @enddate)   < @month_number AND 
         DATEPART(m, @enddate)   > @month_number

This could also be turned into a function (UDF).

user_19457820

one way is to you recursive CTE to generate the various month and count for Jan

declare @date1 datetime = '2018-09-01 00:00:00.000';
declare @date2 datetime = '2019-02-01 00:00:00.000';

; with rcte as
(
    select  [date] = @date1
    union all
    select  [date] = dateadd(month, 1, [date])
    from    rcte
    where   eomonth([date]) < @date2
)
select  count(*)
from    rcte
where   datepart(month, [date]) = 1

or you may use a tally table if you have one

user_1856470
Related