DATEDIFF WEEKS!!!

Question

DATEDIFF WEEKS!!!

flaviosantos

SSC-Addicted

Points: 485
More actions
October 26, 2009 at 6:39 am

#139706

Dear 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...
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 "
Thank u very much
Flávio Santos
Lisbon - Portugal

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

diamondgm SSCrazy Points: 2824 More actions · Answer 1

Hi 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.

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 2

It's the datepart function that's causing you problems, because it's specifically looking at the week within the year. From BOL:

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.

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:

DECLARE @start datetime, @end datetime

SET @start = '1/1/2008'

SET @end = '2/1/2009'

SELECT datediff(ww,@start,@end)

SELECT datepart("ww", dateadd("ww", datediff(ww,@start,@end), @start))

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Greg Milner SSCrazy Points: 2425 More actions · Answer 3

How about something like this:

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 DateCTE

OPTION (MAXRECURSION 0)

G. Milner

flaviosantos SSC-Addicted Points: 485 More actions · Answer 4

Hi 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-29

WeekNumber = 34

First week = 23

last week = 4

Table

Name ---------------------------------------------------------------------------- Week-------------------------------------------------------------

--------------------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---5

JIM 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---5

flaviosantos SSC-Addicted Points: 485 More actions · Answer 5

Hi Everybory,

I got something like my problem, but I dont know how and if it can help me.

http://support.microsoft.com/kb/200299/pt

http://www.rondebruin.nl/weeknumber.htm

Thank u very much !

flaviosantos SSC-Addicted Points: 485 More actions · Answer 6

Link in English

http://support.microsoft.com/kb/200299/en

diamondgm SSCrazy Points: 2824 More actions · Answer 7

Hi Flavio,

Based on that article (http://support.microsoft.com/kb/200299/en), 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.