Oct 9, 2015

Generate Week Dates for a Month in SQL Server

In this post, we will see how to generate Week Start and End dates for a given Month in SQL Server.

Oftentimes one need to generate Start & End dates for all the weeks in a given month to generate week-wise report starting from Monday to Sunday to generate a week wise report

Below is the Table-Valued function where you have to pass year and month and it would return a table with all the weeks and its Start & End Dates for the given month.
CREATE FUNCTION dbo.udf_GetWeekDatesForMonth(
      @Year  SMALLINT
    , @Month TINYINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN   
WITH Dates AS
(
    SELECT  DATEADD(mm,(@Year-1900)*12+@Month-1,0) AS MonthStartDate
          , DATEADD(mm,(@Year-1900)*12+@Month,0)-1 AS MonthEndDate
)
, Weeks AS
(
    SELECT  1 as weekno, MonthStartDate as StartDate
          , CASE WHEN DATEPART(WEEKDAY, MonthStartDate)= 1 THEN MonthStartDate ELSE DATEADD(d, (8 - datepart(WEEKDAY, MonthStartDate)), MonthStartDate) END AS EndDate
    FROM    Dates
    UNION ALL
    SELECT  weekno + 1, DATEADD(d, 1, EndDate)
          , CASE WHEN DATEADD(d, 7, EndDate)<=(select MonthEndDate from Dates) THEN DATEADD(d, 7, EndDate) ELSE (select MonthEndDate from Dates) END
    FROM    Weeks
    where   EndDate < (select MonthEndDate from Dates)
)
SELECT  'Week' + cast(weekno as varchar) as Week, StartDate, EndDate
FROM    Weeks

Let's execute a script to check the output of the function.
SELECT * FROM dbo.udf_GetWeekDatesForMonth(2015,10)
OUTPUT
Output

Refer related post: Generating Week Dates for a year in SQL Server

    Choose :
  • OR
  • To comment
No comments:
Write Comments

Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio