Originally posted by: SystemAdmin

I need to identify the current calendar week regarding saturdays and sundays (gregorian calendar). The FROMDATETIME-Function doesn't regard saturdays and sundays, it only counts seven days per week from the beginning of the year. Does anyone know how to generate the current calendar week regarding saturdays and sundays?
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

So you're saying the formatting string, WW, doesn't work?

=FROMDATETIME(CURRENTDATE(), "{WW}")

I guess you are right as it returns 34 for today and this is week number 35 according to ISO 8601. (The first week of the year is the first week with a Thursday in it.) Did you report this as a bug?

Take a look at http://www.tondering.dk/claus/cal/node7.html#SECTION00770000000000000000

This formula seems to work but how do you get the Julian Day Number in DataStage TX? (Not to be confused with a Julian date which I just did.)
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Its even more confusing as
=FROMDATETIME(CURRENTDATE(), "{WW}")
calculates weeks from the 1/1 ( where week 1 is from the 1/1 to the 7/1 even as this is from thursday to wendsday ) , meaning that week numbers are changeing
the night between wendsday / thursday as it is now.

The workaround isnt to complex though....

=FROMDATETIME(ADDDAYS(CURRENTDATE(),-3), "{WW}")

should correct this if Im not remembering wrong. ( couldbe -4 even ... )

This should have been reported to support some time ago...
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

I tried both -3 & -4 in your map rule and it results in this being Week Number 34. Based on ISO-8601 I believe this week should be Week Number 35.

Julian Day Number from Gregorian Date:
code:1:03c9cd414a=IF(TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{MM}") ) < 3,
TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{DD}") ) + (153 * (TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{MM}") ) +12) - 457) / 5 + 365 * (TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{CCYY}") ) -1) + ( (TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{CCYY}") ) - 1) / 4) - ( (TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{CCYY}") ) - 1) / 100) + ( (TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{CCYY}") ) - 1) / 400) + 1721118.5,
TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{DD}") ) + (153 * TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{MM}") ) - 457) / 5 + 365 * TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{CCYY}") ) + (TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{CCYY}") ) / 4) - (TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{CCYY}") ) / 100) + (TEXTTONUMBER(FROMDATETIME(CURRENTDATE(), "{CCYY}") ) / 400) + 1721118.5 )[/code:1:03c9cd414a]

The following rule seems to work. I only tested it for 12/31/2003, 1/1/2004, & 8/24/2004 which resulted in 1, 1, & 35 respectively.

/* JDN = Julian Day Number */
code:1:03c9cd414a=INT( (MOD(MOD(MOD(MOD( JDN + 31741 - MOD(JDN, 7), 146097), 36524), 1461) - INT( (MOD(MOD(MOD( JDN + 31741 - MOD(JDN, 7), 146097), 36524), 1461)/1460) ), 365) + (MOD(MOD(MOD( JDN + 31741 - MOD(JDN, 7), 146097), 36524), 1461)/1460) ) )/7 + 1[/code:1:03c9cd414a]

Other possible considerations:
Above formulae are based on GMT.
The standard Julian Day begins at noon GMT.
Daylight Savings Time

Have fun. I have real work to do now. Oh, I accept PayPal.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Ah..as I said... not completly sure
but done some testing .. and for this year

=FROMDATETIME(ADDDAYS(CURRENTDATE(),3), "{WW}")
works out...

this have to be changed though depending on wich day the 1 of january is each year...
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Okay, I was using -3 not +3.

This code can tell you the number of the day of the week January 1 fell on.
code:1:218d705ee0= (FIND(FROMDATETIME(TEXTTODATE(FROMDATETIME(CURRENTDATE(), "{CCYY}") + "0101" ), "{DY}" ), "MonTueWedThuFriSatSun") + 2)/3[/code:1:218d705ee0]
There should be a way to use it to produce code that works for all years but leap year would have to be accounted for.

I did find a good tool for testing though. It's a n MS Excel add-in you can download for free from http://www.businessfunctions.co.uk/index.php?e=3. When it's installed, select BusinessFunctions-->Function Finder. Then select WeekNumber in function list window and click on the Example command button at the bottom . A handy spreadsheet loads which will allow you to easily generate results for various parameters such as StartOfWeek(Sun, Mon, etc.) and StartOfWeek1(1st day of year, Thursday(ISO). It also compares their function's results with Excel's WEEKNUM function. It displays two years of results for 12/26 -1/10, the dates that give the most trouble but you can change the middle date for each year to anything you want.[/code]
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: LaurentB

Hi,

I came across this recently, and solved it by using a rule that may prove useful to others. Basically, it is a translation of an algorithm I found on the web.

The attached show a working sample, with two versions of the map, on being more understandable than the other, but less compact. The rule might be improved a bit.

The place where I found the algorithm is the following URL : http://personal.ecu.edu/mccartyr/ISOwdALG.txt
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender