Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Stockholm, Sweden
    Posts
    4

    Unanswered: 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?

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Convert week into date

    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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  4. #4
    Join Date
    Aug 2003
    Location
    Stockholm, Sweden
    Posts
    4
    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 helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •