How to Determine the Number of Days in a Month in SQL Server

How to determine the number of days in a month in SQL Server?

You can use the following with the first day of the specified month:

datediff(day, @date, dateadd(month, 1, @date))

To make it work for every date:

datediff(day, dateadd(day, 1-day(@date), @date),
              dateadd(month, 1, dateadd(day, 1-day(@date), @date)))

How to get number of days in a month in SQL Server

You can use:

select day(eomonth ('2018-02-01')) as NoOfDays

and the result will be:

NoOfDays
-----------
28

How to determine the number of days in a month for a given Date Range?

One approach uses a recusive query. Using date artithmetics, we can build the query so it performs one iteration per month rather than one per day, so this should be a rather efficient approach:

with cte as (
    select 
        datefromparts(year(@dt_start), month(@dt_start), 1) month_start,
        1 - day(@dt_start) + day(
            case when @dt_end > eomonth(@dt_start)
                then eomonth(@dt_start) 
                else @dt_end
            end
        ) as no_days
    union all
    select 
        dateadd(month, 1, month_start),
        case when @dt_end > dateadd(month, 2, month_start) 
            then day(eomonth(dateadd(month, 1, month_start)))
            else day(@dt_end)
        end
    from cte
    where dateadd(month, 1, month_start) <= @dt_end
)
select * from cte

Demo on DB Fiddle.

If we set the boundaries as follows:

declare @dt_start date = '2020-07-10';
declare @dt_end   date = '2020-09-10';

Then the query returns:

month_start | no_days
:---------- | ------:
2020-07-01  |      22
2020-08-01  |      31
2020-09-01  |      10

SQL: Total days in a month

You can get the number of days of a given date like this:

DECLARE @date DATETIME = '2014-01-01'
SELECT DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date))

And the query:

SELECT  ID
        ,[Date]
        ,[Time]
        ,Value1
        ,Value2
        ,DATEDIFF(DAY, [Date], DATEADD(MONTH, 1, [Date])) AS TotalDayinMonth
        ,Value1 * 100 * DATEDIFF(DAY, [Date], DATEADD(MONTH, 1, [Date])) * Value2 AS Result
FROM yourTable

Getting number of days for a specific month and year between two dates SQL

The generic formula for the number of overlapping days in two ranges is

MAX(MIN(end1, end2) - MAX(start1, start2) + 1, 0)

In your case you have one set of Start and End dates, you must construct the other from the given month and year using datefromparts and eomonth.

Unfortunately SQL Server doesn't support LEAST and GREATEST formulas as do MySQL and Oracle, so this is a bit painful to implement. Here's an example using variables:

declare @month int;
declare @year int;
declare @startDate date;
declare @endDate date;
declare @startOfMonth date;
declare @endOfMonth date;
declare @minEnd date;
declare @maxStart date;
set @month = 1;
set @year = 2020;
set @startDate = '2019-11-12';
set @endDate = '2020-01-13';
set @startOfMonth = datefromparts(@year, @month, 1)
set @endOfMonth = eomonth(@startOfMonth)
set @minEnd = case when @endDate < @endOfMonth then @endDate
                   else @endOfMonth
              end;
set @maxStart = case when @startDate < @startOfMonth then @startOfMonth
                     else @startDate
                end;
select case when datediff(day, @maxStart, @minEnd) + 1 < 0 then 0
            else datediff(day, @maxStart, @minEnd) + 1
       end as days_in_month

Output:

13

Demo on dbfiddle; this includes other sample date ranges.

You could implement something similar using a series of CTEs if the values are derived from a table.

SQL Server query for total number of days for a month between date ranges

Ideally, you have a table named "Dates" with all the dates you will ever use, e.g. year 1950 through 2100. This query will give you the result you want:

  select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
    from dates d
    join ranges r on d.thedate between r.[from date] and r.[to date]
group by datediff(m, 0, d.thedate)
order by themonth;

Result:

|   themonth | COLUMN_1 |
-------------------------
| 2012-11-01 |        9 |
| 2012-12-01 |        1 |

Note that instead of just showing "11" or "12" as month, which doesn't work well if you have ranges going above 12 months, or doesn't help sorting when it crosses a new year, this query shows the first day of the month instead.

If not, you can virtually create a dates table on the fly, per the expanded query below:

;with dates(thedate) as (
  select dateadd(yy,years.number,0)+days.number
    from master..spt_values years
    join master..spt_values days
      on days.type='p' and days.number < datepart(dy,dateadd(yy,years.number+1,0)-1)
   where years.type='p' and years.number between 100 and 150
      -- note: 100-150 creates dates in the year range 2000-2050
      --       adjust as required
)
  select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
    from dates d
    join ranges r on d.thedate between r.[from date] and r.[to date]
group by datediff(m, 0, d.thedate)
order by themonth;

The full working sample is given here: SQL Fiddle

Number of days of a week inside a month in SQL

This is sort of a SQL Server setting (or trick) which works because the 1st of January, 1900 was a Monday. Since that's where SQL Server starts counting from it makes it easier to locate the first Thursday of any month. Thanks to Jeff Moden btw. I got this from something he wrote. Maybe there's a better way to this now, idk

with iso_dts_cte(yr, mo, wk) as (
    select * from (values ('2020', '12', '50'),
                          ('2020', '12', '51'),
                          ('2020', '12', '52'),
                          ('2020', '12', '53'),
                          ('2021', '01', '01'),
                          ('2021', '01', '02'),
                          ('2021', '01', '03')) v(yr, mo, wk))
select iso.*, v.*
from iso_dts_cte iso
     cross apply (values (cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk-1)*7) as date), 
                          cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date))) v(start_dt, end_dt);

yr      mo  wk  start_dt    end_dt
2020    12  50  2020-12-07  2020-12-13
2020    12  51  2020-12-14  2020-12-20
2020    12  52  2020-12-21  2020-12-27
2020    12  53  2020-12-28  2021-01-03
2021    01  01  2021-01-04  2021-01-10
2021    01  02  2021-01-11  2021-01-17
2021    01  03  2021-01-18  2021-01-24

To expand the week ranges into days and then count by calendar year and calendar month you could try something like this.

[Edit] It's my understanding the date hierarchy you're looking for is 1) calendar year, 2) calendar month, 3) iso week. The output seems to match the example now. However, there's not a way to ORDER BY to display like the example.

with
iso_dts_cte(yr, mo, wk) as (
    select * from (values ('2020', '12', '50'),
                          ('2020', '12', '51'),
                          ('2020', '12', '52'),
                          ('2020', '12', '53'),
                          ('2021', '01', '01'),
                          ('2021', '01', '02'),
                          ('2021', '01', '03')) v(yr, mo, wk)),
days_cte(n) as (
    select * from (values (1),(2),(3),(4),(5),(6),(7)) v(n))
select year(dt.calc_dt) cal_yr, month(dt.calc_dt) cal_mo, iso.wk, count(*) day_count
from iso_dts_cte iso
     cross apply (values (cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk-1)*7) as date), 
                          cast(dateadd(wk,datediff(wk,0,'01/04/'+iso.yr),0)+((iso.wk)*7)-1 as date))) v(start_dt, end_dt)
     cross join days_cte d
     cross apply (values (dateadd(day, d.n-1, v.start_dt))) dt(calc_dt)
group by year(dt.calc_dt), month(dt.calc_dt), iso.wk;

cal_yr  cal_mo  wk  day_count
2020    12      50  7
2020    12      51  7
2020    12      52  7
2020    12      53  4
2021    1       01  7
2021    1       02  7
2021    1       03  7
2021    1       53  3

Number of days left in current month

Simply use the Datepart function:

declare @date date
set @date='16 Nov 2016'
select datediff(day, @date, dateadd(month, 1, @date)) - Datepart(DAY,@date) 

---

Related Topics