Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATEDIFF WEEKS!!!


DATEDIFF WEEKS!!!

Author
Message
flaviosantos
flaviosantos
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
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
diamondgm
diamondgm
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 938
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
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1424 Visits: 19324
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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
Greg Milner
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 491
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
flaviosantos
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
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
flaviosantos
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
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
flaviosantos
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Link in English

http://support.microsoft.com/kb/200299/en
diamondgm
diamondgm
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 938
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search