CODEWITHSUNDEEP
sql-server-2008t-sqlcounting
user3306489
user3306489

Reputation: 157

Count first day of month between two dates

Could someone advise how I can go about counting the number of first day of month between two dates?

For example: 02/01/2015 to 05/05/2015 will count as 4.

Upvotes: 1

Views: 825

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

It can be accomplished easily like this:

DECLARE @sd DATE = '02/02/2015', @ed DATE = '05/01/2015'
SELECT DATEDIFF(mm, @sd, @ed) + CASE WHEN DAY(@sd) = 1 THEN 1 ELSE 0 END

Upvotes: 5

jpw
jpw

Reputation: 44881

If you have a sequence of dates stored somewhere (like in a calendar table) you can easily count the dates matching the day(date) predicate. If you don't have any suitable table with a date sequence you can use a recursive common table expression to generate one on the fly like this:

declare @start date 
set @start = '02/01/2015'

declare @end date 
set @end = '05/05/2015'

;with dates (date) as (
    select @start date
    union all 
    select dateadd(day, 1, date) as date from dates where date < @end
    )

select count(*) no_of_firsts from dates where day(date) = 1

Sample SQL Fiddle

Upvotes: 3

Related Questions