Hi,
Which brings up the question: Is there a function YEAR_ISO ??? Or some
other
function which returns the ISO year, week and weekday together? I couldn't
find any ...
if you are already on release 6.1 you may use the scalar function
VARCHAR_FORMAT, which converts a timestamp into any character representation
you want. It is also possible to convert the timestamp into Year of the ISO
week, ISO week and numeric ISO week day (Monday = 1 and Sunday = 7)
The following SQL-Statement will return Year of the ISO week / ISO week and
ISO week day:
Select VarChar_Format(Current_Timestamp, 'IYYY / IW ID') CurrDate,
VarChar_Format(Timestamp_ISO('2008-12-29'), 'IYYY / IW ID')
Dat20081229,
VarChar_Format(Timestamp_ISO('2005-01-01'), 'IYYY / IW ID')
Dat20050101,
From SysIbm/SysDummy1;
Result:
CurrDate = '2009/02 3'
Dat20081229 = '2009/01 3'
Dat20050101 = '2004/53 6'
The following statement will only return the Year of the ISO Week:
Select VarChar_Format(Current_Timestamp, 'IYYY') CurrDate,
VarChar_Format(Timestamp_ISO('2008-12-29'), 'IYYY') Dat20081229,
VarChar_Format(Timestamp_ISO('2005-01-01'), 'IYYY') Dat20050101,
From SysIbm/SysDummy1;
Result:
CurrDate = '2009'
Dat20081229 = '2009'
Dat20050101 = '2004'
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Ulrich Krueger
Gesendet: Tuesday, 06. January 2009 20:25
An: 'Midrange Systems Technical Discussion'
Betreff: RE: Week_ISO and the last week of 2008
I just found the following Wikipedia entry, which explains the rules and the
exceptions to the rules governing week 1 or 53 in a year:
http://en.wikipedia.org/wiki/ISO_week_date
According to that, '12/29/2008' is year 2009, week 1, weekday 1.
More info about ISO 8601 at
http://en.wikipedia.org/wiki/ISO_8601
In other words: User beware! If you are building an application that's based
on the ISO number of the week you will end up with (apparently) unexpected /
incorrect results. Be sure to also include an ISO - based calculation of the
year's value, so that a date like '12/29/2008' returning ISO-week '1'
doesn't throw your calculations in disarray ... that's because hidden behind
the week '1' value there is a year value of '2009'!
Which brings up the question: Is there a function YEAR_ISO ??? Or some other
function which returns the ISO year, week and weekday together? I couldn't
find any ...
Regards,
Ulrich Krueger
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Walden H. Leverich
Gesendet: Tuesday, 06. January 2009 18:48
An: Midrange Systems Technical Discussion
Betreff: Week_ISO and the last week of 2008
According to the spec week_iso should have returned 53 for the end of
2008. On V5R4 I'm getting 1. Can someone please try:
select week_iso(date('12/29/08')) from sysibm/sysdummy1
on their machine and let me know what they get.
Thanks,
-Walden
midrange.com
