Monday, October 5, 2009

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





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)




Return @firstDayOfWeek




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



  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


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



No comments:

Post a Comment