Hello,

I am trying to get the week number for a particular date field but I am not getting the right week for jan 01.
for example, 20150101 the week number should be 1 but it is returning 52.
20160101 the week number should be 1 but it is returning 52.
any other days seems to be fine.

calendar_yywk = CAL_YEAR * 100 +HPART(HDTTM( FULL_DATE,8,'HYYMDIA'),'WEEK', 'I2')---> for 20150101 is returning 201552, 20160101 returning 201652
where CAL_YEAR I4 is DATE_INT / 10000, FULL_DATE is the date in YYMD format and DATE_INT is the FULL_DATE in integer format.

Does anyone knows how to solve this?


thanks,

V

This message has been edited. Last edited by: vnf,

Do a Google search for 'week numbers for 2015' and you will find



The first days of a year are generally included in the last days of the previous year.

The behaviour is correct. What's more of a challenge is to get the correct year printed with that week number at the start or end of a year.

Several RDBMS's provide a separate variant of a to_char pattern to extract the year for use with a week number.
For example, to_char(CURRENT_DATE, 'IYYY/IW') gives the ISO-8601 year/week, which results in year 2014 for week 53, regardless of whether we're looking at 2014/12/31 or at 2015/01/01.

In contrast, to_char(CURRENT_DATE, 'YYYY/IW'), which gives the calendar year with the week (for week 53 in 2014, that incorrectly gives 2015 for 2015/01/01)