For the specified serial number which includes the string representing year 2007 and week 44, both solutions return a date that falls on a Monday. The first solutioin returns '11/05/07' and the second returns '10/29/07'. When these dates are converted back to the week number, the first is Monday of week 45 and the second is Monday of week 44.

set nocount on

DECLARE @Serial CHAR(12)

SET @Serial = 'SER074400001'

SELECT DATEADD(wk,DATEDIFF(wk,0,DATEADD(yy,CAST(SUBSTRING(@Serial,4,2) AS INT),'2000'))+CAST(SUBSTRING(@Serial,6,2)AS INT),0)

go

DECLARE @Serial CHAR(12)

SET @Serial = 'SER074400001'

--------------------------------------------------------------------

-- Set date for Monday of the Week

-- Compute days = week * 7 - 5

-- Have to allow for weekday of first of the year in calculation.

--------------------------------------------------------------------

select dateadd(day,

(convert(int,substring(@Serial,6,2)) * 7) - 5

- datepart(weekday,convert(datetime,('01/01/'+ substring(@Serial,4,2)))),

convert(datetime,('01/01/'+ substring(@Serial,4,2))))

go

select datepart(week,'2007-11-05') 'Week', datepart(weekday,'2007-11-05') 'Week Day'

select datepart(week,'2007-10-29') 'Week', datepart(weekday,'2007-10-29') 'Week Day'