date - Teradata Get Week Number using SQL Code -

i wish week number date in teradata can see there solutions use sys calendar table use code week number

week 28 example 6th 12th of july inclusive

i have found code works fine when put date instead of cdate

select  ((cdate - ((extract(year cdate) - 1900) * 10000 + 0101 (date))) -     ((cdate - date '0001-01-07') mod 7)  + 13) / 7 

my date in format mm/dd/yy , when run error message

select ((06/29/15 - ((extract(year 06/29/15) - 1900) * 10000 + 0101     (date))) - ((06/29/15 - date '0001-01-07') mod 7)  + 13) / 7 

"user cannot perform operation on date"

any appreciated

a date format used casting string (= display), datatype date has no format, it's integer.

06/29/15 not date, it's calculation based on integers 6 / 29 / 15 results in integer zero.

dates specified in iso format: date '2015-06-29':

((date '2015-06-29' - ((extract(year date '2015-06-29') - 1900) * 10000 + 0101     (date))) - ((date '2015-06-29' - date '0001-01-07') mod 7)  + 13) / 7 

but week seems based on international standard , calculation not return correct number last days of year, e.g. wednesday 2014-12-31 week 53 while should week 1 in iso. if iso weeks needed better use

weeknumber_of_year (date '2015-06-29', 'iso') -- integer or to_char(date '2015-06-29', 'iw')  -- string 

edit:

those functions added in td14, before can use following code iso week/year:

replace function iso_week(cdate date) returns int specific iso_week_date returns null on null input contains sql deterministic collation invoker inline type 1 return                        (((cdate - ((cdate - date '0001-01-01') mod 7) + 5)   - ((extract(year (cdate - ((cdate - date '0001-01-01') mod 7) + 5)) - 1900) * 10000 + 0101 (date))) / 7) + 1  (format '99') ;  replace function iso_year(cdate date) returns int specific iso_year_date returns null on null input contains sql deterministic collation invoker inline type 1 return     extract (year (cdate - (((cdate - date '0001-01-01') mod 7) + 1) + 4)) (format '9999') ; 

if you're not allowed create sql udfs can cut&paste calculation.