Convert week into date

In my database I am storing a delivery week in format YYYYWW, ie '200245'. Now I want to display this as a date.
I want the first date for the week to display as DD MMM YYYY, ie '04 Nov 2002'.
Does anyone know any simple way to do this?Originally posted by Pedalen
In my database I am storing a delivery week in format YYYYWW, ie '200245'. Now I want to display this as a date.

I want the first date for the week to display as DD MMM YYYY, ie '04 Nov 2002'.

Does anyone know any simple way to do this?

You can use a T-SQL statement such as:
DECLARE @.WK_DTE CHAR(6),
@.YR CHAR(4),
@.WK INT
SET @.WK_DTE = '200245'
SET @.YR = LEFT(@.WK_DTE,4)
SET @.WK = CAST(RIGHT(@.WK_DTE,2) AS INT)
SELECT CONVERT(CHAR(11), DATEADD(WEEK,@.WK,'01/01/' + @.YR), 106)

you could put this into a function or a stored procedure|||You need to subtract 1 week. If you run the datepart function on your result you'll see that it yields week number 46, not 45.

You also need to find the start of the week.

declare @.YEARWEEK char(6)
set @.YEARWEEK = '200346'

declare @.weekstring char(6)
set @.weekstring = '200345'
declare @.weekstart datetime
set @.weekstart = dateadd(ww, cast(Right(@.weekstring, 2) as int)-1, '01/01/' + left(@.weekstring, 4))
set @.weekstart = dateadd(d, 1-datepart(dw, @.weekstart), @.weekstart)

blindman|||Originally posted by blindman
You need to subtract 1 week. If you run the datepart function on your result you'll see that it yields week number 46, not 45.

You also need to find the start of the week.

declare @.YEARWEEK char(6)
set @.YEARWEEK = '200346'

declare @.weekstring char(6)
set @.weekstring = '200345'
declare @.weekstart datetime
set @.weekstart = dateadd(ww, cast(Right(@.weekstring, 2) as int)-1, '01/01/' + left(@.weekstring, 4))
set @.weekstart = dateadd(d, 1-datepart(dw, @.weekstart), @.weekstart)

blindman

Thanks, this helpssqlsql