• Digs (1/6/2013)


    First the code...

    DECLARE @Current INT

    DECLARE @Prev1 INT

    DECLARE @Prev2 INT

    DECLARE @Prev3 INT

    DECLARE @Prev4 INT

    DECLARE @Prev5 INT

    DECLARE @Prev6 INT

    DECLARE @Prev7 INT

    DECLARE @Prev8 INT

    DECLARE @Prev9 INT

    DECLARE @Prev10 INT

    DECLARE @Prev11 INT

    SET @Current = YEAR(getUTCDate()) * 100 + DATEPART(week,getUTCDate())

    SET @Prev1 = YEAR(DATEADD(d, -7,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -7,getUTCDate()))

    SET @Prev2 = YEAR(DATEADD(d, -14,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -14,getUTCDate()))

    SET @Prev3 = YEAR(DATEADD(d, -21,getUTCDate())) * 100 + DATEPART(week,DATEADD(d, -21,getUTCDate()))

    SELECT @Current, @Prev1, @Prev2, @Prev3

    SELECT YEAR(getUTCDate())*100 + DATEPART(week,getUTCDate()) AS 'WeekNo'

    The result from the 1st SELECT is this:

    201302201253201252201251

    As you can see I am missing 201301

    QUESTION: how can I fix this? I need YYYYMM in order.

    PS: I dont have a Calender table with YYYYMM, unles you know where I can get one ?

    Any ideas

    As some of this others have pointed out, you've listed a format of YYYYMM as being what is desired but your code and output seem to indicate that what you really want is YYYYWW. Now the question becomes, what is your definition for the first week of the year?

    --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)