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
Change is inevitable... Change for the better is not.