September 26, 2010 at 6:14 am
There may be a difference between microsoft week and iso week !
try this:
declare @dates table ( dt datetime not null )
Declare @startdate datetime
set @startdate = '2010-01-01'
insert into @dates
select DATEADD(DD, N, @startdate)
from master.dbo.fn_ALZDBA_Tally(0, 500, 1) -- tally table value function ( see Lynn Pettis http://www.sqlservercentral.com/articles/T-SQL/67899/)
select DATEPART(WEEK, dt) as [week]
, DATEPART(ISOWK, dt) as [ISOweek]
, *
from @dates
order by dt ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 26, 2010 at 7:36 am
ALZDBA (9/26/2010)
There may be a difference between microsoft week and iso week !...
Ther definitely is a difference:
The "old-fashioned" week function will calculate the weeks starting with week 1 for the week holding January 1st whereas ISO week start with week 1 being the week with at least four days. So, if January 1st is a Friday (like this year) the result of the week function will be different to the ISO week.
Furthermore, ISO weeks always start with Monday, whereas week depends on the setting of DATEFIRST.
Fortunately, SS2K8 now has the ISOWK built-in function so we don't have to write our own anymore (I don't like the ISO week function described in BOL since it's not a iTVF...)
September 26, 2010 at 9:17 am
Thank you for your replies. I have modified my code as follow:
Select CAST((b.Week ) as int)as WeekOfYear,CAST(a.dateID as int) as DateID, CAST( b.year as CHAR(4)) as Year , a.StyleCode, cast(a.WarehouseID as varchar(23)) as WarehouseID, a.Quantity
From InventoryLookup a
Join (
Select datepart(ISOWK,CONVERT(DATETIME,CONVERT(varchar,DateID),112)) Week, datepart(YEAR,CONVERT(DATETIME,CONVERT(varchar,DateID),112)) year, StyleCode, WarehouseID, MAX(DateID) DateID
From InventoryLookup
Group by datepart(YEAR,CONVERT(DATETIME,CONVERT(varchar,DateID),112)), datepart(ISOWK,CONVERT(DATETIME,CONVERT(varchar,DateID),112)), StyleCode, WarehouseID
) b on b.DateID = a.DateID and b.StyleCode = a.StyleCode and b.WarehouseID = a.WarehouseID
where --a.DateID>=20100809 and a.DateID<=20100815 and
a.StyleCode='RU901511000161'
Order by b.Week, b.year , a.StyleCode, a.WarehouseID
But when I run this resultset then my DateID 20100809 weekofyear value is 32. But it should be 33. Right?
September 26, 2010 at 10:27 am
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply