Oct 9, 2015

Generate Week Dates for a Year in SQL Server

In this post, we will see how to generate week start and end dates for a given year in SQL Server.

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

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

Lets' execute the script and check the output
SELECT * FROM dbo.udf_GetWeekDatesForYear(2015)
OUTPUT
Output

Refer related post: Generating Week Dates for a month 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