How to determine First Day of the week For a Week Number and Year in SQL

How to determine First Day of the week For a Week Number and Year in SQL?

 

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,

@InputWeekNo int

)

RETURNS DATETIME

BEGIN

 

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)

End

 

 

Return @firstDayOfWeek

 

END

 

Logical Description:

The logic needs to be separated into three parts:

1. 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'
   

   
    

2. Get the First Day of the Year.
   

   The function DateName () is used to get the Day in text format. (Sunday, Monday etc.)
   

   
    

3. Add 7 * (WeekNumber -1) days to the first date of year(In this example, '1/1/2010') – Where WeekNumber is the input.
   

   
    

4. 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

Output:

2010-01-17 00:00:00.000

Formatted Display:

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

Output:

01/17/2010