ISO calendar
567571Mar 6 2009 — edited Mar 16 2009I have this query where I want to retrieve the ISO calendar date for the 1st Jan 2009:
{noformat}SELECT To_Char((SYSDATE - 64) + 2, 'IYIWD') FROM Dual
{noformat}This returns the result of 09016 as expected.
Unfortunately, the system we use has its own way to interprete the ISO week: basically, the iso week starts on a Saturday and for the year 2008 had 53 weeks instead of 52.
So, we would want the result for the first of Jan to be 08536 and then the 3rd Jan 2009 to be 09011 and so on, remembering that the year 2009 will only have 52 weeks and not 53.
It is pretty much annoying me because I cannot get it right.
I have tried all sorts of sombinations removing the +2, using nls_territory...anything I could think of.
Does anyone have any idea, please?
Thanks