SQL Server Generate Week Start Dates End Dates for a Year or Month in SQL Server
Sometimes, you might require Start & End dates for all the weeks in a given year or month to generate week-wise report starting from Monday to SundayI have developed a 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 a given year.
Getting Month Week Start Date and End Date
- - Totals by Week
CREATE FUNCTION dbo.GetWeekDatesForMonth
(@Year SMALLINT, @Month TINYINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
cteMonth AS (SELECT MonthStart = DATEADD(mm,(@Year-1900)*12+@Month-1,0)
,MonthEnd = DATEADD(mm,(@Year-1900)*12+@Month,0)-1)
,cteWeeks AS (SELECT MonthStart
,MonthEnd
,FirstWeekStart = DATEADD(dd,DATEDIFF(dd,0,MonthStart)/7*7,0)
,LastWeekStart = DATEADD(dd,DATEDIFF(dd,0,MonthEnd )/7*7,0)
FROM cteMonth)
SELECT [Week] = 'Week'+LEFT(ca.Wk+1,1)
,StartDate = CAST(
CASE
WHEN ca.Wk > 0
THEN DATEADD(dd,Ca.Wk*7,FirstWeekStart)
ELSE MonthStart
END
AS DATE)
,EndDate = CAST(
CASE
WHEN DATEADD(dd,Ca.Wk*7+6,FirstWeekStart) <= MonthEnd
THEN DATEADD(dd,Ca.Wk*7+6,FirstWeekStart)
ELSE MonthEnd
END
AS DATE)
FROM cteWeeks
CROSS APPLY (SELECT TOP (DATEDIFF(dd,FirstWeekStart,LastWeekStart)/7+1)
t.N
FROM (VALUES (0),(1),(2),(3),(4),(5))t(N))ca(Wk);
Using GetWeekDatesForMonth
-- Lets' execute the script and check the output
SELECT * FROM dbo.GetWeekDatesForMonth(2015,12)
-- OR You can use the following function to get week start and end date
CREATE FUNCTION dbo.udf_GetWeekDatesForMonth(
@Year SMALLINT
, @Month TINYINT
)
RETURNS @WeekDates TABLE (
Week VARCHAR(5)
, StartDate DATE
, EndDate DATE
)
AS
BEGIN
DECLARE @MonthStartdate DATE
, @MonthEnddate DATE
, @WeekStartDate DATE
, @WeekEndDate DATE
, @ctr INT = 0
SET @MonthStartdate = CAST(CAST(@Year*100+@Month AS VARCHAR)+'01' AS DATE)
SET @MonthEnddate = DATEADD(d,-1,DATEADD(m,1, @MonthStartdate))
SET @WeekStartDate = @MonthStartdate
SET @WeekEndDate = DATEADD(d, (8 - datepart(WEEKDAY, @WeekStartDate)), @WeekStartDate)
WHILE @WeekStartDate < @MonthEnddate
BEGIN
SET @ctr = @ctr + 1
SET @WeekEndDate = (CASE WHEN @WeekEndDate > @MonthEnddate THEN @MonthEnddate ELSE @WeekEndDate END)
INSERT INTO @WeekDates
SELECT 'Week' + cast(@ctr as varchar), @WeekStartDate, @WeekEndDate
SET @WeekStartDate = DATEADD(d, 1, @WeekEndDate)
SET @WeekEndDate = DATEADD(d, 6, @WeekStartDate)
END
RETURN
END
- -Lets' execute the script and check the output
SELECT * FROM dbo.udf_GetWeekDatesForMonth(2015,12)
Generate Week Dates for a Year in SQL Server
CREATE FUNCTION dbo.udf_GetWeekDatesForYear(
@Year SMALLINT
)
RETURNS @WeekDates TABLE (
Week VARCHAR(10)
, StartDate DATE
, EndDate DATE
)
AS
BEGIN
DECLARE @YearStartdate DATE
, @YearEnddate DATE
, @WeekStartDate DATE
, @WeekEndDate DATE
, @ctr INT = 0
SET @YearStartdate = CAST(CAST(@Year AS VARCHAR)+'0101' AS DATE)
SET @YearEnddate = DATEADD(day,-1,DATEADD(year,1, @YearStartdate))
SET @WeekStartDate = @YearStartdate
SET @WeekEndDate = DATEADD(day, (8 - datepart(WEEKDAY, @WeekStartDate)), @WeekStartDate)
WHILE @WeekStartDate < @YearEnddate
BEGIN
SET @ctr = @ctr + 1
SET @WeekEndDate = (CASE WHEN @WeekEndDate > @YearEnddate THEN @YearEnddate ELSE @WeekEndDate END)
INSERT INTO @WeekDates
SELECT 'Week-' + cast(@ctr as varchar), @WeekStartDate, @WeekEndDate
SET @WeekStartDate = DATEADD(d, 1, @WeekEndDate)
SET @WeekEndDate = DATEADD(d, 6, @WeekStartDate)
END
RETURN
END
- -Lets' execute the script and check the output
SELECT * FROM dbo.udf_GetWeekDatesForYear(2015)
No comments:
Post a Comment