January 6, 2013 at 11:12 am
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
January 6, 2013 at 11:46 am
select CONVERT(CHAR(6), getdate(), 112)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 6, 2013 at 12:33 pm
Thanks for your interest in my problem.
But that does not produce a sequential YYYYMM sequence!
Any other idea
January 6, 2013 at 1:24 pm
Digs (1/6/2013)
First the code.......
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
You ask for a date in format YYYYMM but your query generate the date in strange format YYYYWW where WW 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?
January 6, 2013 at 1:29 pm
Digs (1/6/2013)
First the code...PS: I dont have a Calender table with YYYYMM, unles you know where I can get one ?
Any ideas
plenty of calendar scripts available if you search...heres a snippet....see last column
E&OE
regards
--==== Create a Tally table and a Calendar table
SELECT TOP 60001 IDENTITY(INT, 1, 1) AS N --=== will have start as 1...alter to IDENTITY(INT, 0, 1) for zero as start
INTO dbo.Tally
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
SET DATEFORMAT DMY
DECLARE @Date_Start AS DATETIME
DECLARE @Date_End AS DATETIME
SET @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_Start
FROM dbo.Tally t
WHERE t.N - 1 + @Date_Start <= @Date_End
UPDATE dbo.Calendar
SET 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)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 6, 2013 at 1:51 pm
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.
January 6, 2013 at 2:29 pm
CREATE TABLE [dbo].[mydate](
[mydate] [datetime] NULL
)
INSERT INTO [dbo].[mydate]([mydate])
SELECT '20121222 00:00:00.000' UNION ALL
SELECT '20121223 00:00:00.000' UNION ALL
SELECT '20121224 00:00:00.000' UNION ALL
SELECT '20121225 00:00:00.000' UNION ALL
SELECT '20121226 00:00:00.000' UNION ALL
SELECT '20121227 00:00:00.000' UNION ALL
SELECT '20121228 00:00:00.000' UNION ALL
SELECT '20121229 00:00:00.000' UNION ALL
SELECT '20121230 00:00:00.000' UNION ALL
SELECT '20121231 00:00:00.000' UNION ALL
SELECT '20130101 00:00:00.000' UNION ALL
SELECT '20130102 00:00:00.000' UNION ALL
SELECT '20130103 00:00:00.000' UNION ALL
SELECT '20130104 00:00:00.000' UNION ALL
SELECT '20130105 00:00:00.000' UNION ALL
SELECT '20130106 00:00:00.000' UNION ALL
SELECT '20130107 00:00:00.000' UNION ALL
SELECT '20130108 00:00:00.000' UNION ALL
SELECT '20130109 00:00:00.000'
SELECT mydate ,
YEAR( mydate ) * 100 + DATEPART( week , mydate )
FROM mydate;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 6, 2013 at 3:21 pm
Sorry YYYYWW not YYYYMM
thanks for reponses
January 7, 2013 at 10:47 am
To get the correct week number based on ISO standard you can use this function:
--Credit to Ramakrishna Elashwarapu
CREATE FUNCTION dbo.tvfGetWeekNumberFromDate_ISO
(
@dDate DATETIME
)
RETURNS @WeekNumber TABLE
(
[Year] INT NULL
,[WeekNumber] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
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
Then just do a CROSS APPLY and ROW_NUMBER() to sort:
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[mydate] DATETIME NULL,
PRIMARY KEY (ID))
INSERT INTO #TempTable
SELECT '20121222 00:00:00.000' UNION ALL
SELECT '20121223 00:00:00.000' UNION ALL
SELECT '20121224 00:00:00.000' UNION ALL
SELECT '20121225 00:00:00.000' UNION ALL
SELECT '20121226 00:00:00.000' UNION ALL
SELECT '20121227 00:00:00.000' UNION ALL
SELECT '20121228 00:00:00.000' UNION ALL
SELECT '20121229 00:00:00.000' UNION ALL
SELECT '20121230 00:00:00.000' UNION ALL
SELECT '20121231 00:00:00.000' UNION ALL
SELECT '20130101 00:00:00.000' UNION ALL
SELECT '20130102 00:00:00.000' UNION ALL
SELECT '20130103 00:00:00.000' UNION ALL
SELECT '20130104 00:00:00.000' UNION ALL
SELECT '20130105 00:00:00.000' UNION ALL
SELECT '20130106 00:00:00.000' UNION ALL
SELECT '20130107 00:00:00.000' UNION ALL
SELECT '20130108 00:00:00.000' UNION ALL
SELECT '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 OriginalDate
FROM
#TempTable AS tt
CROSS APPLY
dbo.tvfGetWeekNumberFromDate_ISO(tt.mydate) AS w
WHERE
tt.ID > 0
Output:
RowNumYearWeekOriginalDate
12012512012-12-22 00:00:00.000
22012512012-12-23 00:00:00.000
32012522012-12-24 00:00:00.000
42012522012-12-25 00:00:00.000
52012522012-12-26 00:00:00.000
62012522012-12-27 00:00:00.000
72012522012-12-28 00:00:00.000
82012522012-12-29 00:00:00.000
92012522012-12-30 00:00:00.000
102013012012-12-31 00:00:00.000
112013012013-01-01 00:00:00.000
122013012013-01-02 00:00:00.000
132013012013-01-03 00:00:00.000
142013012013-01-04 00:00:00.000
152013012013-01-05 00:00:00.000
162013012013-01-06 00:00:00.000
172013022013-01-07 00:00:00.000
182013022013-01-08 00:00:00.000
192013022013-01-09 00:00:00.000
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply