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. Ex: If the year is 2010, the date would be '1/1/2010'


     

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


     


  3.  

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

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

Post a Comment