How to determine First Day of the week From Week Number and Year in SQL Server?
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
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
|
No responses found. Be the first to respond and make money from revenue sharing program.
|