Home Forums SQL Server 7,2000 Administration SQL Year and Week numbers RE: SQL Year and Week numbers

  • August 11, 2008 at 7:10 pm

    #856443

    jennyor (8/11/2008)

    I'm trying to write an update command in sql to take the weekyear portion of a serial # and convert it back to a date by extracting the week and year "SER074400001" (0744). I can't seem to get the week to calculate back. As you can see below the closest I have come gives me (44/2007).

    Any assistance would be appreciated.

    update field_fact

    set shipdate = substring(serln, 6, 2) + '/20' + SUBSTRING(serln, 4, 2)

    --SELECT substring(serln, 6, 2) +'/20' + SUBSTRING(serln, 4, 2) AS date

    FROM field_fact

    WHERE (NOT (serln LIKE 'n%') AND NOT (serln LIKE '1%'))

    Jenny

    Hi, Jenny, and welcome aboard!

    First, just as an FYI... you'd probably do better if you posted you problem separately... it'll get more attention that way. Also, doesn't apply this time, but take a look at the link in my signature line below for when it might.

    As to your problem, I think this might do what you want provided that your week starts on Monday...

    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)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)