Urgent: Current week number 2

[raphaelj]

Hi,

I need a formula to get the current week-number. How can I do that?

Thank you in advance

Raphael

 

[jdemmi]

I don't see anyway to do this.....there is nothing to reference against. You could create a HUGE formula which says If date is between 01/01 (month,day) and 01/07 then week = 1...etc etc but this would be tedious AND it would change from year to year. Let me know if you find a way. I could use this too!

 

[jdemmi]

I FOUND OUT A WAY.....IF you are using Oracle. I am not sure if there is similar functionality in MS-SQL or other DB products. Nor am I quite sure how to apply this into a Crsyatl report. But this query...

SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL

will give you the week # for today's date.

 

[mbarron]

If you are using version 8 or higher (I don't know if the DateDiff function is available in earlier versions) you can use:

datediff("ww",date(year(currentdate),01,01),currentdate)+1 Mike

 

[chelseatech]

What do you define a week number as? If it just the number of weeks since Jan 1 then
Truncate(CurrentDate - Date(Year(CurrentDate),1,1)/7)

If you need to set it to a specific day of the week (E.g. Friday) then any date field minus the DayOfWeek of that field always get you back to the previous Saturday.

dateVar d:= Date(Year(currentdate),1,1);
d:= d - DayofWeek(d) ; // back to prior saturday
d:=d + 6 // Now we are on the Next Friday

Truncate(CurrentDate- d)/7)
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[synapsevampire]

As Chelseatech alludes to, this *can* be a fairly complicated subject based on the business rules.

Work Weeks differ and are determined by company guidelines, and if this fuzzy ole mind recalls accurately, the ISO standard is that the week which contains the 4th day of the year is WW 1.

SQL Server does NOT use this method, nor do any of the above formulas. Oracle might.

I believe that the good news is that you can set the start day of a week and the starting work week settings within Oracle. Crystal also allows for this where intervaltype = 'ww':

DatePart (intervalType, inputDateTime, firstDayOfWeek, firstWeekOfYear)

This is a great example for using a peiod table in your database as it resolves this and many similar nuisances, plus it allows for the elusive presentation of data within Crystal for periods NOT available in the data (show months that don't have data).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

I should have referenced that the datepart function is used against currentdate to determine the current week.

datepart('ww', currentdate)

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com