CODEWITHSUNDEEP
sql-serversql-server-2012
Muhammad Ali
Muhammad Ali

Reputation: 873

Date From Week Number and Day In Sql Server 2012

I have day number of the week as well as week number of the year .How can i calculate date of that day in sql .For Example.the day number of 22-Feb-2014 is 7th and it is 8th week of the year.Now how can i calculate the date back from this information.Its urgent.Please help.I want query.

Upvotes: 4

Views: 2065

Answers (2)

adopilot
adopilot

Reputation: 4510

Here is my verison

create FUNCTION date_from_week_number_day 
(
     @year int = 2014
    ,@weeknumber int = 8 
    ,@day int = 7


)
RETURNS date
AS
BEGIN


    declare
         @date date
        ,@first_date_of_year date


set @first_date_of_year = CONVERT(date,cast(@year as varchar(4))+'.1.1') --First calculate first day of year


set @date =    dateadd(dd,@day -- we add days acoring of day of week
                        ,dateadd(dd,-1*DATEPART(WEEKDAY,@first_date_of_year) --first day of year is not first day of week so we go back days how day has pass
                            , DATEADD(WEEK,@weeknumber-1,@first_date_of_year) --1. we go to week before last
                            )
                        )

return @date

END

you can call this 

SET DATEFIRST  7
select dbo.date_from_week_number_day(2014,8,7)

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

  CREATE FUNCTION dbo.Date_From_WN_DN (@YearNum int,@WeekNum int,@DayNum int)
        RETURNS Date AS
    BEGIN
        DECLARE @FirstDayYear As Date;
        SET @FirstDayYear='01/01/' + CAST(@YearNum As varchar)
        RETURN dateadd(d,((@DayNum)-datepart(weekday,@FirstDayYear)),dateadd(week, @WeekNum-1,@FirstDayYear))
    END


    SET DATEFIRST 7
    SELECT dbo.DEV_VW_WeekSerial (2014,8,7)

O/P :

enter image description here

Upvotes: 1

Related Questions