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

convert rows to columns

Hi All,

I have two tables

TblEmployee

EmpID,

EmpName

TblAddress

AddressID

EmpID

Address1

Address2

AddressType (will hold values like ‘TP’, ‘HP’ storing different address types)

If I do a simple join assuming 2 different address types exists for each employee. I would be getting two rows. But I need address1 and address2 of different address types as four columns I mean address1 of type ‘TP’, address2 of type ‘TP’, address1 of type ‘HP’, address2 of type ‘HP’

along with employee information.

Can some body help me with the query.select *
from TblEmployee e
inner join (
select EmpID,
TP_Address1 = max(case when AddressType = 'TP' then Address1 end),
TP_Address2 = max(case when AddressType = 'TP' then Address2 end),

HP_Address1 = max(case when AddressType = 'HP' then Address1 end),

HP_Address2 = max(case when AddressType = 'HP' then Address2 end)
from TblAddress
group by EmpID
) a
on e.EmpID = a.EmpID|||Thanks a lot K H Tan