All Forums Database
sunny.j 90 posts Joined 10/10
05 Jan 2016
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
 
 
 
 
 
 
 

dnoeth 4628 posts Joined 11/04
05 Jan 2016

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')

 

Dieter

sunny.j 90 posts Joined 10/10
06 Jan 2016

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 .

sunny.j 90 posts Joined 10/10
06 Jan 2016

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

Fred 1096 posts Joined 08/04
06 Jan 2016

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.

dnoeth 4628 posts Joined 11/04
06 Jan 2016

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.

Dieter

sunny.j 90 posts Joined 10/10
07 Jan 2016

thanks alot it's useful and working

You must sign in to leave a comment.