ISO calendar : previous week number issue

Database

ISO calendar : previous week number issue

Dears

i'm using ISO date calendar in my data analysis ;

Observed that in ISO calendar for the end of the week of December -2015 i.e  for the dates 28/12/2015 to 03/12/2016

week number : 53 and Year : 2015

when we try today that is on 05.01.2016 to know the previous week we got as week: 52 by using a udf  ( udf in c langauge: udf_add_weeks)

when i try with teradata functions i got wrong result

sel td_week_of_year(date '2015-12-31') -1 : 51

sel td_week_of_year(date) : 01

sel td_week_of_year(datecol) -1 : 0  -- datecol : 04/01/2016

is there any function to know the previous week number even if we follow the ISO date calendar ;

kindly suggest me due to this issue my  data analysis and KPI are wrong .

thanks and regards

Sunny

6 REPLIES
N/A

Re: ISO calendar : previous week number issue

Hi Sunny,

those functions are not based on ISO.

Use the Business Calendar Functions instead:

WEEKNUMBER_OF_YEAR(datecol, 'iso'), 
YEARNUMBER_OF_CALENDAR(datecol, 'iso')

Btw, for the previous week number there's no need for a C-UDF :)

WEEKNUMBER_OF_YEAR(datecol - 7, 'iso')

Re: ISO calendar : previous week number issue

Hi Dnoeth

thanks alot for your reply .

is it possible to know the previous week number by using the current week in the format of YYYYWW

ex:

201601 preivous week 201553 according to ISO

would you please suggest me on the same .

Re: ISO calendar : previous week number issue

can any one suggest me to find the preivous weeknumber  from the current week in the format of YYYYWW.

i request for the forum help

Teradata Employee

Re: ISO calendar : previous week number issue

There are no supplied functions that take yyyyww as an argument.

But since we know 04 Jan is always in week 1, we can derive a DATE that we know must fall in week yyyyww:

CAST(

((ywcol/100 /* extract yyyy from integer yyyyww */) -1900 /* convert to cyy form */) *10000 /* shift to cyy0000 */

+104 /* make it cyy0104 for 04 Jan of that year */

AS DATE )

+7*((ywcol MOD 100 /* extract ww */) - 1) /* offset by number of days between week 1 and ww */

Then you can subtract 7 days and use WEEKNUMBER_OF_YEAR as above.

N/A

Re: ISO calendar : previous week number issue

Hi Fred,

this works fine for weeks, as it returns any day within that week.

I did a pair of SQL-UDFs a few years ago to return the first day of the ISO week, i.e. monday:

-- 201335
REPLACE FUNCTION isoweek_to_date(isoweek INT)
RETURNS DATE
SPECIFIC isoweek_to_date_I
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
((((isoweek / 100) -1900) * 10000 + 104) (DATE))
+ (((isoweek MOD 100) - 1) * 7)
- ((((((isoweek / 100) -1900) * 10000 + 104) (DATE)) - DATE '0001-01-01') MOD 7)
;

-- '2013W35'
REPLACE FUNCTION isoweek_to_date(isoweek VARCHAR(7))
RETURNS DATE
SPECIFIC isoweek_to_date_c
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(((CAST(SUBSTRING(isoweek FROM 1 FOR 4) AS INT) -1900) * 10000 + 104) (DATE))
+ ((CAST(SUBSTRING(isoweek FROM 6 FOR 2) AS INT) - 1) * 7)
- (((((CAST(SUBSTRING(isoweek FROM 1 FOR 4) AS INT) -1900) * 10000 + 104) (DATE)) - DATE '0001-01-01') MOD 7)
;

Would be much easier if TO_DATE supported the 'IYYYIW' format, too.

Re: ISO calendar : previous week number issue

thanks alot it's useful and working