The following SQL User defined function describes how to determine the first day of the week using Week Number and Year as Input.
Function Name: GetFirstDayOfAWeekInYear
Input Parameters: @InputYear – Year Number
@InputWeekNo – Week Number
Output Type: DATETIME – First day of week
Example: GetFirstDayOfAWeekInYear(2010,4) Will return 01/17/2010 as output.
Created By: Vijayalakshmi Rajkumar
Created On: 10/5/2009
CREATE FUNCTION [dbo].[GetFirstDayOfAWeekInYear]
( @InputYear int,
declare @firstDayOfYear as datetime;
declare @firstDayOfWeek as datetime, @TempDate as datetime;
declare @firstDayNameOfYear AS varchar(50);
declare @defaultDate as varchar(50);
--get the first day of year
SET @defaultDate = '1/1/' + Convert(Varchar, @InputYear)
--Add the 1/1 to the Input Year to get the first day of year
SET @firstDayOfYear =CONVERT (DATETIME, @defaultDate)
--Get the day name of the year - It can be one among 'Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday'
SET @firstDayNameOfYear = DATENAME(dw, @firstDayOfYear)
-- Add 7 * ( WeekNo -1) days
SET @TempDate = DateAdd(DD, (7 * (@InputWeekNo - 1)),@firstDayOfYear)
--Based on First Day of Year we need to subtract the number of days
SELECT @firstDayOfWeek = CASE @firstDayNameOfYear
When 'Monday' Then DateDiff(DD, 1,@TempDate)
When 'Tuesday' Then DateDiff(DD, 2,@TempDate)
When 'Wednesday' Then DateDiff(DD, 3,@TempDate)
When 'Thursday' Then DateDiff(DD, 4,@TempDate)
When 'Friday' Then DateDiff(DD, 5,@TempDate)
When 'Saturday' Then DateDiff(DD, 6 ,@TempDate)
When 'Sunday' Then DateDiff(DD, 7,@TempDate)
The logic needs to be separated into three parts:
- Get the first date of the year by attaching '1/1/' with the Year
Ex: If the year is 2010, the date would be '1/1/2010'
- Get the First Day of the Year.
The function DateName () is used to get the Day in text format. (Sunday, Monday etc.)
- Add 7 * (WeekNumber -1) days to the first date of year(In this example, '1/1/2010') – Where WeekNumber is the input.
- Based on the first day of the year, the number of days needs to be subtracted from the date
For Monday -1 day
Tuesday – 2 days
Wednesday – 3 days
Thursday – 4 days
Friday – 5 days
Saturday – 6 days
Sunday – 7 days
How to Call?
The above function can be called as follows:
Select [dbo].[GetFirstDayOfAWeekInYear](2010,4) – Where 2010 is the Year and 4 is the week number
The date display can be formatted to display in various formats.
To display the output in MM/DD/YYYY format, we can call the function as follows:
SELECT Convert (varchar(10), [dbo].[GetFirstDayOfAWeekInYear](2010,4),101) --Format MM/DD/YYYY