InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 08:19:25 GMT20http://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspxBuild [i]your own[/i] Calendar table with a calendar date as the PRIMARY KEY, then add other columns to show whatever your business needs in the way of temporal information. I add a column for the ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. CREATE TABLE Something_Report_Periods(something_report_name CHAR(10) NOT NULL PRIMARY KEY CHECK (something_report_name LIKE <pattern>), something_report_start_date DATE NOT NULL, something_report_end_date DATE NOT NULL, CONSTRAINT date_ordering CHECK (something_report_start_date <= something_report_end_date),etc);These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'There are several websites with calendars you can cut & paste if you do not want to take 20 minutes to build it yourself, but you can start your search with: http://www.calendar-365.com/week-number.htmlMon, 07 Jan 2013 11:15:10 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspxTo get the correct week number based on ISO standard you can use this function:[code="sql"]--Credit to Ramakrishna ElashwarapuCREATE FUNCTION dbo.tvfGetWeekNumberFromDate_ISO ( @dDate DATETIME )RETURNS @WeekNumber TABLE ( [Year] INT NULL ,[WeekNumber] INT NULL )WITH SCHEMABINDINGASBEGIN DECLARE @iWeekdayNumber INT ,@dCurrThurs DATETIME ,@YearFirstThurs DATETIME ,@iYearFirstThursWeekNum INT ,@dFirstThursYear DATETIME ,@iWeekNumber INT SET @iWeekdayNumber = (((DATEPART(dw, @dDate) - 1) + (@@DATEFIRST - 1)) % 7) + 1 SET @dCurrThurs = DATEADD(d,(4 - @iWeekdayNumber),@dDate) SET @YearFirstThurs = CAST(CAST(YEAR(@dCurrThurs) AS CHAR(4)) + '-01-01' AS DATETIME) SET @iYearFirstThursWeekNum = (((DATEPART(dw, @YearFirstThurs) - 1) + (@@DATEFIRST - 1)) % 7) + 1 IF (@iYearFirstThursWeekNum in (1,2,3,4)) SET @dFirstThursYear = DATEADD(d,(4 - @iYearFirstThursWeekNum),@YearFirstThurs) ELSE SET @dFirstThursYear = DATEADD(d,(4 - @iYearFirstThursWeekNum + 7),@YearFirstThurs) SET @i[WeekNumber] = DATEDIFF(d,@dFirstThursYear,@dCurrThurs)/7+1 INSERT INTO @WeekNumber SELECT YEAR(@dFirstThursYear),@iWeekNumber RETURN END[/code]Then just do a CROSS APPLY and ROW_NUMBER() to sort:[code="sql"]IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [ID] INT IDENTITY(1,1) NOT NULL, [mydate] DATETIME NULL, PRIMARY KEY (ID))INSERT INTO #TempTableSELECT '20121222 00:00:00.000' UNION ALLSELECT '20121223 00:00:00.000' UNION ALLSELECT '20121224 00:00:00.000' UNION ALLSELECT '20121225 00:00:00.000' UNION ALLSELECT '20121226 00:00:00.000' UNION ALLSELECT '20121227 00:00:00.000' UNION ALLSELECT '20121228 00:00:00.000' UNION ALLSELECT '20121229 00:00:00.000' UNION ALLSELECT '20121230 00:00:00.000' UNION ALLSELECT '20121231 00:00:00.000' UNION ALLSELECT '20130101 00:00:00.000' UNION ALLSELECT '20130102 00:00:00.000' UNION ALLSELECT '20130103 00:00:00.000' UNION ALLSELECT '20130104 00:00:00.000' UNION ALLSELECT '20130105 00:00:00.000' UNION ALLSELECT '20130106 00:00:00.000' UNION ALLSELECT '20130107 00:00:00.000' UNION ALLSELECT '20130108 00:00:00.000' UNION ALLSELECT '20130109 00:00:00.000' SELECT ROW_NUMBER() OVER (ORDER BY w.[Year],w.[WeekNumber]) AS RowNum ,CAST(w.[Year] AS CHAR(4)) + (CASE WHEN LEN(w.[WeekNumber]) = 1 THEN '0'+CAST(w.[WeekNumber] AS CHAR(1)) ELSE CAST(w.[WeekNumber] AS CHAR(2)) END) AS YearWeek ,mydate AS OriginalDateFROM #TempTable AS ttCROSS APPLY dbo.tvfGetWeekNumberFromDate_ISO(tt.mydate) AS wWHERE tt.ID > 0 [/code]Output:[code="xml"]RowNum YearWeek OriginalDate1 201251 2012-12-22 00:00:00.0002 201251 2012-12-23 00:00:00.0003 201252 2012-12-24 00:00:00.0004 201252 2012-12-25 00:00:00.0005 201252 2012-12-26 00:00:00.0006 201252 2012-12-27 00:00:00.0007 201252 2012-12-28 00:00:00.0008 201252 2012-12-29 00:00:00.0009 201252 2012-12-30 00:00:00.00010 201301 2012-12-31 00:00:00.00011 201301 2013-01-01 00:00:00.00012 201301 2013-01-02 00:00:00.00013 201301 2013-01-03 00:00:00.00014 201301 2013-01-04 00:00:00.00015 201301 2013-01-05 00:00:00.00016 201301 2013-01-06 00:00:00.00017 201302 2013-01-07 00:00:00.00018 201302 2013-01-08 00:00:00.00019 201302 2013-01-09 00:00:00.000[/code] Mon, 07 Jan 2013 10:47:40 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspxSorry YYYYWW not YYYYMMthanks for reponsesSun, 06 Jan 2013 15:21:08 GMTDigshttp://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspx[code="sql"]CREATE TABLE [dbo].[mydate]( [mydate] [datetime] NULL) INSERT INTO [dbo].[mydate]([mydate])SELECT '20121222 00:00:00.000' UNION ALLSELECT '20121223 00:00:00.000' UNION ALLSELECT '20121224 00:00:00.000' UNION ALLSELECT '20121225 00:00:00.000' UNION ALLSELECT '20121226 00:00:00.000' UNION ALLSELECT '20121227 00:00:00.000' UNION ALLSELECT '20121228 00:00:00.000' UNION ALLSELECT '20121229 00:00:00.000' UNION ALLSELECT '20121230 00:00:00.000' UNION ALLSELECT '20121231 00:00:00.000' UNION ALLSELECT '20130101 00:00:00.000' UNION ALLSELECT '20130102 00:00:00.000' UNION ALLSELECT '20130103 00:00:00.000' UNION ALLSELECT '20130104 00:00:00.000' UNION ALLSELECT '20130105 00:00:00.000' UNION ALLSELECT '20130106 00:00:00.000' UNION ALLSELECT '20130107 00:00:00.000' UNION ALLSELECT '20130108 00:00:00.000' UNION ALLSELECT '20130109 00:00:00.000' SELECT mydate , YEAR( mydate ) * 100 + DATEPART( week , mydate ) FROM mydate;[/code]Sun, 06 Jan 2013 14:29:59 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspx[quote][b]Digs (1/6/2013)[/b][hr]First the code...[code="sql"] 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'[/code]The result from the 1st SELECT is this:201302 201253 201252 201251As you can see I am missing 201301QUESTION: 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[/quote]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?Sun, 06 Jan 2013 13:51:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspx[quote][b]Digs (1/6/2013)[/b][hr]First the code...PS: I dont have a Calender table with YYYYMM, unles you know where I can get one ?Any ideas[/quote]plenty of calendar scripts available if you search...heres a snippet....see last columnE&OEregards[code="sql"]--==== Create a Tally table and a Calendar tableSELECT TOP 60001 IDENTITY(INT, 1, 1) AS N --=== will have start as 1...alter to IDENTITY(INT, 0, 1) for zero as startINTO dbo.TallyFROM sys.all_columns ac1CROSS JOIN sys.all_columns ac2CROSS JOIN sys.all_columns ac3ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100SET DATEFORMAT DMYDECLARE @Date_Start AS DATETIMEDECLARE @Date_End AS DATETIMESET @Date_Start = '01/01/2010'SET @Date_End = '31/12/2020'CREATE TABLE dbo.Calendar ( calendar_date_ID INT IDENTITY(1, 1) NOT NULL, calendar_week_ID INT, calendar_month_ID INT, calendar_date DATETIME PRIMARY KEY CLUSTERED, calendar_year SMALLINT, calendar_month TINYINT, calendar_day TINYINT, calendar_quarter TINYINT, first_day_in_month DATETIME, last_day_in_month DATETIME, day_of_week TINYINT, week_of_year TINYINT, days_in_month TINYINT, day_of_year SMALLINT, is_weekday INT, day_name VARCHAR (10), month_name VARCHAR (10), iso_date CHAR (8), fiscal_year SMALLINT, fiscal_month TINYINT, DIGS_MTH INT );INSERT INTO dbo.Calendar (calendar_date)SELECT t.N - 1 + @Date_StartFROM dbo.Tally tWHERE t.N - 1 + @Date_Start <= @Date_EndUPDATE dbo.CalendarSET calendar_week_ID = calendar_date_id / 7 + 1, calendar_year = Datepart (YEAR, calendar_date), fiscal_year = CASE WHEN Datepart(M, calendar_date) >= 10 THEN Datepart (YEAR, calendar_date) + 1 ELSE Datepart (YEAR, calendar_date) END, calendar_month = Datepart (MONTH, calendar_date), fiscal_month = CASE WHEN Datepart(M, calendar_date) >= 10 THEN Datepart(M, calendar_date) - 9 ELSE Datepart(M, calendar_date) + 3 END, calendar_day = Datepart (DAY, calendar_date), calendar_quarter = Datepart (QUARTER, calendar_date), first_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date), 0), last_day_in_month = Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1, day_of_week = Datepart (WEEKDAY, calendar_date), week_of_year = Datepart (WEEK, calendar_date), day_of_year = Datepart (DAYOFYEAR, calendar_date), is_weekday = Isnull (( CASE WHEN ( ( @@DATEFIRST - 1 ) + ( Datepart (WEEKDAY, calendar_date) - 1 ) )%7 NOT IN ( 5, 6 ) THEN 1 END ), 0), day_name = Datename (WEEKDAY, calendar_date), month_name = Datename (MONTH, calendar_date), iso_date = CONVERT(CHAR(8), calendar_date, 112), days_in_month = Datepart(dd, ( Dateadd(mm, Datediff(mm, 0, calendar_date) + 1, 0) - 1 )), DIGS_MTH = CONVERT(CHAR(6), calendar_date, 112)[/code]Sun, 06 Jan 2013 13:29:19 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspx[quote][b]Digs (1/6/2013)[/b][hr]First the code.......The result from the 1st SELECT is this:201302 201253 201252 201251As you can see I am missing 201301QUESTION: 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[/quote]You ask for a date in format [b]YYYYMM [/b]but your query generate the date in strange format [b]YYYYWW [/b]where [b]WW [/b]mean week number.Your query will return values that you are expecting day after tomorrow :-)Today we have 06 january 2013 if you substract 7 days, then we have?Sun, 06 Jan 2013 13:24:34 GMTe4d4http://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspxThanks for your interest in my problem.But that does not produce a sequential YYYYMM sequence!Any other ideaSun, 06 Jan 2013 12:33:29 GMTDigshttp://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspx[code="sql"]select CONVERT(CHAR(6), getdate(), 112)[/code]Sun, 06 Jan 2013 11:46:49 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1403343-391-1.aspxFirst the code...[code="sql"] 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'[/code]The result from the 1st SELECT is this:201302 201253 201252 201251As you can see I am missing 201301QUESTION: 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 ideasSun, 06 Jan 2013 11:12:53 GMTDigs