InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:36:45 GMT20http://www.sqlservercentral.com/Forums/Topic808633-338-1.aspxHi Flavio,Based on that article ([url]http://support.microsoft.com/kb/200299/en[/url]), it would seem that your error is a result of bug in a VB library.Looking closer at your code, you are using a VB function - DATEDIFF (that have the same name in T-SQL). Perhaps you would like to use SQL code to populate your variable?numWeek = datediff("ww",startProject,EndProject) ==>PSUEDO:int numWeek;string s_SQL;string s_SQL = "SELECT datediff("ww",startProject,EndProject)";numWeek = Execute(s_SQL);I'm sorry, but I don't know VB syntax so I've written it in a pseudo syntax of sorts.Doing this should avoid the bug in Oleaut32.dll, unless SQL Server is making use of this DLL too? I don't know tha answer to that.Seniors, please advise if I am making any mistakes here. Thank you.Wed, 28 Oct 2009 12:20:52 GMTdiamondgmhttp://www.sqlservercentral.com/Forums/Topic808633-338-1.aspxLink in Englishhttp://support.microsoft.com/kb/200299/enWed, 28 Oct 2009 10:27:41 GMTflaviosantoshttp://www.sqlservercentral.com/Forums/Topic808633-338-1.aspxHi Everybory, I got something like my problem, but I dont know how and if it can help me.http://support.microsoft.com/kb/200299/pthttp://www.rondebruin.nl/weeknumber.htmThank u very much !Wed, 28 Oct 2009 10:26:21 GMTflaviosantoshttp://www.sqlservercentral.com/Forums/Topic808633-338-1.aspxHi DiamondDgm, thanks. I hope somebody help us...And I wouldn't like to want this(DATEDIFF(YY, StartDate, EndDate) * 52) + DATEDIFF(WW, StartDate, EndDate)Eg.A Project has startDate = 2009-06-01 and endDate = 2010-01-29WeekNumber = 34First week = 23last week = 4Table[b]Name [/b] ---------------------------------------------------------------------------- [b] Week[/b]---------------------------------------------------------------------------------23--24--25--26--27--28--29- 30--31--32--33--34--35--36---37--38--39--40--41--42--43--44--45--46--47--48--49--50--51---52--53---2--3---4 ARTUR CARVALHO--- 5---3---5---5---5---5---5---5---5----5---5---5---5---4----5----5---3---0----2---5---5---5---5---5---5---5---4----4----5---4---4---5---5---5JIM CARTER--------- 5---3---5---5---5---5---5---5---5----5---5---5---5---4----5----5---3---0----2---5---5---5---5---5---5---5---4----4----5---4---4---5---5---5Wed, 28 Oct 2009 10:12:27 GMTflaviosantoshttp://www.sqlservercentral.com/Forums/Topic808633-338-1.aspxHow about something like this: [code="sql"]with DateCTE as( select cast('01/01/1998' as datetime) DateValue union all select DateValue + 1 from DateCTE where DateValue + 1 < '1/1/2016')Select cast(CAST(DateValue AS CHAR(11)) AS DateTime) AS [FullDate], DATEPART(wk, DateValue) AS WeekOfYear, DATEPART(yy, DateValue) AS CalendarYear from DateCTEOPTION (MAXRECURSION 0)[/code]Tue, 27 Oct 2009 22:47:02 GMTGreg Milnerhttp://www.sqlservercentral.com/Forums/Topic808633-338-1.aspxIt's the datepart function that's causing you problems, because it's specifically looking at the week within the year. From BOL:[quote]The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year. [/quote]Can't say as I fully understand why you need this broken out in this manner, but if you just returned the datediff instead of the datepart, you'd get your correct numbers above 52. Example:[code="sql"]DECLARE @start datetime, @end datetimeSET @start = '1/1/2008'SET @end = '2/1/2009'SELECT datediff(ww,@start,@end)SELECT datepart("ww", dateadd("ww", datediff(ww,@start,@end), @start))[/code]Tue, 27 Oct 2009 09:09:49 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic808633-338-1.aspxHi Flávio,If I understand your problem correctly, when DATEDIFF(WW, StartDate, EndDate) > 52, you experience a rollover? (eg 53 => 1)I can't seem to replicate your problem, and perhaps a senior here could help out in telling us why this might be happening to you.Else, you might want to consider using:(DATEDIFF(YY, StartDate, EndDate) * 52) + DATEDIFF(WW, StartDate, EndDate)I don't think that this is the best approach to solving your problem and would really like to see what the seniors have to say.Mon, 26 Oct 2009 23:39:12 GMTdiamondgmhttp://www.sqlservercentral.com/Forums/Topic808633-338-1.aspxDear Friends, I would like to know if somebody can help me. I need to generate the number of weeks in a table, we have projects bigger than 1 year, when I use the DateDiff, its return the weeks but when are in the week 53, its skip the week number 1... [b] Set rs = CreateObject("ADODB.Recordset") sSQL = "SELECT Disp.Colaborador Colaborador , " numWeek = datediff("ww",startProject,EndProject) for i = 0 to numWeek - 1 weeknumber = datepart("ww", dateadd("ww", i, startProject)) sSQL = sSQL + " SUM(CASE WHEN DATEPART(ww, Data) = " & weeknumber & " THEN 1 ELSE 0 END) [" & weeknumber & "], " next sSQL = sSQL + "COUNT(*) [Total] " sSQL = sSQL + "FROM [Disponibilidade RH] Disp " sSQL = sSQL + "LEFT OUTER JOIN RH ON Disp.Colaborador = RH.Colaborador " sSQL = sSQL + "WHERE TIPO = '"& ProjectName &"'" sSQL = sSQL + "GROUP by Disp.Colaborador " sSQL = sSQL + "ORDER BY Disp.Colaborador "[/b]Thank u very muchFlávio SantosLisbon - PortugalMon, 26 Oct 2009 06:39:46 GMTflaviosantos