Given year, month, day and week number how to find the date?(Sql Server 2005 Set based)

Question

Given year, month, day and week number how to find the date? e.g.

year = 2010  

month = Feb  
day = Wed   
week number = 4, how can I find that the date is 24/02/2010

Thanks

Answer 1

try this:

--given info
DECLARE @Year        varchar(4) --= 2010  
DECLARE @MonthName   varchar(10)  --= Feb  
DECLARE @WeekDayday  varchar(10) --= Wed   
DECLARE @WeekNumber  int         --=4
SET @Year        ='2010'
SET @MonthName   ='Feb'  
SET @WeekDayday  ='Wed'   
SET @WeekNumber  =4


--used to solve
DECLARE @StartDate datetime
       ,@EndDate   datetime
       ,@FirstWeek int

SET @StartDate='01 '+@MonthName+' '+@Year
SET @EndDate=@StartDate+38
SET @FirstWeek=DATENAME(week,@StartDate)-1

;with AllDates AS
(
    SELECT @StartDate AS DateOf, DATENAME(week,@StartDate)-@FirstWeek AS WeekOf, DATENAME(weekday,@StartDate) AS WeekDayOf
    UNION ALL
    SELECT DateOf+1, DATENAME(week,DateOf+1)-@FirstWeek AS WeekOf, DATENAME(weekday,DateOf+1) AS WeekDayOf
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT
    DateOf ,WeekOf ,WeekDayOf
    FROM AllDates
    WHERE WeekOf=@WeekNumber AND WeekDayOf LIKE @WeekDayday+'%'
    ORDER BY DateOf

OUTPUT

DateOf                  WeekOf      WeekDayOf
----------------------- ----------- ------------------------------
2010-02-24 00:00:00.000 4           Wednesday

(1 row(s) affected)