How to get the number of week in month amd month ?

Here's the promised code. On my db server, this code lives in a Reference_Tables database. Change the USE statement to reflect your own paradigm.

First, is the IsHoliday scalar function. If you don't need federal holidays, you can skip this, but if you do need this, you must add this function *before* adding the stored proc below.

USE [Reference_Tables]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_IsHoliday]    Script Date: 01/18/2017 10:39:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- This function returns a bit value of 1 if the specified date is a federal 
-- holiday, or a 0 if it isn't a holiday.
CREATE FUNCTION [dbo].[fn_IsHoliday]
(
    @date  date
)
RETURNS bit
AS
BEGIN

    DECLARE @year  int = DATEPART(YEAR, @date);
    DECLARE @month int = DATEPART(MONTH,@date);
    DECLARE @day   int = DATEPART(DAY, @date);
    DECLARE @dayName varchar(12) = DATENAME(DW, @date );

    DECLARE @nthWeekDay int = ceiling(@day / 7.0);
    DECLARE @isThursday bit = CASE WHEN @dayName LIKE 'Thursday' THEN 1 ELSE 0 END;
    DECLARE @isFriday   bit = CASE WHEN @dayName LIKE 'Friday' THEN 1 ELSE 0 END;
    DECLARE @isSaturday bit = CASE WHEN @dayName LIKE 'Saturday' THEN 1 ELSE 0 END;
    DECLARE @isSunday   bit = CASE WHEN @dayName LIKE 'Sunday' THEN 1 ELSE 0 END;
    DECLARE @isMonday   bit = CASE WHEN @dayName LIKE 'Monday' THEN 1 ELSE 0 END;
    DECLARE @isWeekend  bit = CASE WHEN @isSaturday = 1 OR @isSunday = 1 THEN 1 ELSE 0 END;
     
    ---- New Years Day
    if (@month = 12 AND @day = 31 AND @isFriday=1) return 1;
    if (@month = 1 AND @day = 1 AND @isWeekend=0) return 1;
    if (@month = 1 AND @day = 2 AND @isMonday=1) return 1;

    ---- MLK day
    if (@month = 1 AND @isMonday = 1 AND @nthWeekDay = 3) return 1;

    ------ President’s Day ( 3rd Monday in February )
    if (@month = 2 AND @isMonday = 1 AND @nthWeekDay = 3) return 1;

    ------ Memorial Day ( Last Monday in May )
    if (@month = 5 AND @isMonday = 1 AND DATEPART(MONTH, DATEADD(DAY, 7, @Date)) = 6) return 1;

    ------ Independence Day ( July 4 )
    if (@month = 7 AND @day = 3 AND @isFriday = 1) return 1;
    if (@month = 7 AND @day = 4 AND @isWeekend = 0) return 1;
    if (@month = 7 AND @day = 5 AND @isMonday = 1) return 1;

    ------ Labor Day ( 1st Monday in September )
    if (@month = 9 AND @isMonday = 1 AND @nthWeekDay = 1) return 1;

    ------ Columbus Day ( 2nd Monday in October )
    if (@month = 10 AND @isMonday = 1 AND @nthWeekDay = 2) return 1;

    ------ Veteran’s Day ( November 11 )
    if (@month = 11 AND @day = 10 AND @isFriday = 1) return 1;
    if (@month = 11 AND @day = 11 AND @isWeekend = 0) return 1;
    if (@month = 11 AND @day = 12 AND @isMonday = 1) return 1;

    ------ Thanksgiving Day ( 4th Thursday in November )
    if (@month = 11 AND @isThursday = 1 AND @nthWeekDay = 4) return 1;

    ------ Christmas Day ( December 25 )
    if (@month = 12 AND @day = 24 AND @isFriday = 1) return 1;
    if (@month = 12 AND @day = 25 AND @isWeekend = 0) return 1;
    if (@month = 12 AND @day = 25 AND @isMonday = 1) return 1;

    return 0;
END

GO

This stored procedure returns a table of dates based on the specified date range (the date range can be a single day if desired).

USE [Reference_Tables]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetCalendar]    Script Date: 01/18/2017 10:38:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetCalendar]
    @startDate DATE, -- the start of the date range
    @endDate   DATE -- the end of the date range
AS
BEGIN
    SET NOCOUNT ON;
    
    -- sanity check - if the start date is not earlier than or equal to the 
    -- end date, switch them around
    if (@startDate > @endDate)
    BEGIN
        DECLARE @temp date = @startDate;
        SET @startDate = @endDate;
        SET @endDate = @temp;
    END
    
    ;WITH n AS 
    (
        SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
        FROM sys.all_objects
    )
    SELECT DATEADD(DAY, n-1, @startDate) AS CalendarDate
           ,DATEPART(YEAR, DATEADD(DAY, n-1, @startDate)) AS CalendarYear
           ,DATEPART(MONTH, DATEADD(DAY, n-1, @startDate)) AS CalendarMonth
           ,DATEPART(QUARTER, DATEADD(DAY, n-1, @startDate)) AS CalendarQuarter
           ,DATEPART(DAYOFYEAR, DATEADD(DAY, n-1, @startDate)) As CalendarJulianDay
           ,DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate)) AS FiscalDate
           ,DATEPART(YEAR, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalYear
           ,DATEPART(MONTH, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalMonth
           ,DATEPART(QUARTER, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalQuarter
           ,DATEPART(DAYOFYEAR, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) As FiscalJulianDay
           ,DATENAME(MONTH, DATEADD(DAY, n-1, @startDate)) AS [MonthName]
           ,DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) AS [DayOfMonth]
           ,DATEPART(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayNumber
           ,DATENAME(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayName
           ,ceiling(DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) / 7.0) As NthWeekday
           -- if you didn't need the IsHoliday function, simply comment out 
           -- the following line
           ,dbo.fn_IsHoliday(DATEADD(DAY, n-1, @startDate)) AS IsHoliday
    FROM n;
END

GO

To get the nth week (in a month) in which a date falls, you can use the returned NthWeekday column.

How coincidental - I just wrote some SQL code that creates a calendar, based on the specified date range. It's nothing more than a stored proc and a function that you implement in your database. Simply call the stored proc, and it returns the "calendar", which includes the calendar year, month, day, julian day and quarter, the fiscal year, month, quarter and julian day, as well as the nth weekday, nth week, and whether or not the date is a federal holiday (accounting for holidays that fall on weekends).

The business case that spurred this development was that the analysts were using a table that had data that didn't go any further than Dec 31 2016, and that had to manually be updated every year. My approach requires no tables, and can return calendar info for any range of dates.

When I return to work on Monday, I'll post it here.

Hello,

Simple code:

// This presumes that weeks start with Monday.
// Week 1 is the 1st week of the year with a Thursday in it.
public static int GetIso8601WeekOfYear(DateTime time)
{
    // Seriously cheat.  If its Monday, Tuesday or Wednesday, then it'll 
    // be the same week# as whatever Thursday, Friday or Saturday are,
    // and we always get those right
    DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(time);
    if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
    {
        time = time.AddDays(3);
    }

    // Return the week of our adjusted day
    return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
} 

Read more: ISO 8601 Week of Year format in Microsoft .Net – I'm not a Klingon[^]

Regards,
@jafc