get week of year

[jliang]

Hi,

Does Teradata have function to get the week number without join calendar table? such as, day of '2004-08-02' is the week 31.

Thanks very much

Jing

 

[BillDHS]

Although I don't know the exact algorithum, you could probable take the stored integer date, remove the year indicator 98,99,100 and divide by 7. You would still have to deal with the day-of-week that the year started on.

 

[tdatgod]

if you look at the Database sys_calendar and follow the Views back to through to the base table

show view calendar;

show view calendartmp;

you will see week_of_year is calculated as day of year adjusted for the first day of the calendar

(day_of_year - (day_of_calendar + 0) mod 7 + 6) / 7,


show view CALBASICS;

and then you will see day_of_year is calculated as

(case (cdate mod 10000)/100
when 1 then cdate mod 100
when 2 then cdate mod 100 + 31
when 3 then cdate mod 100 + 59
when 4 then cdate mod 100 + 90
when 5 then cdate mod 100 + 120
when 6 then cdate mod 100 + 151
when 7 then cdate mod 100 + 181
when 8 then cdate mod 100 + 212
when 9 then cdate mod 100 + 243
when 10 then cdate mod 100 + 273
when 11 then cdate mod 100 + 304
when 12 then cdate mod 100 + 334
end)
+
(case
when ((((cdate / 10000 + 1900) mod 4 = 0) AND ((cdate / 10000 + 1900)
mod 100 <> 0)) OR
((cdate / 10000 + 1900) mod 400 = 0)) AND ((cdate mod 10000)/100
> 2) then
1
else
0
end)

and day of calendar is calulates as.....


case
when (((cdate mod 10000) / 100) > 2) then
(146097 * ((cdate/10000 + 1900) / 100)) / 4
+(1461 * ((cdate/10000 + 1900) - ((cdate/10000 + 1900) / 100)*100) )
/ 4
+(153 * (((cdate mod 10000)/100) - 3) + 2) / 5
+ cdate mod 100 - 693901
else
(146097 * (((cdate/10000 + 1900) - 1) / 100)) / 4
+(1461 * (((cdate/10000 + 1900) - 1) - (((cdate/10000 + 1900) - 1) /
100)*100) ) / 4
+(153 * (((cdate mod 10000)/100) + 9) + 2) / 5
+ cdate mod 100 - 693901
end


I guess if you took these 2 formulas and put them all together you could come up with a single SQL statement which could calulate day of week without having to do the join.


------

 

[jliang]

Thanks very much for your help.

J

 

[dnoeth]

I always wondered who created those strange calculations ;-)

day_of_year may be easily calculated using
date - [first day of year] + 1:

cdate + 1 - ((extract(year from cdate) - 1900) * 10000 + 0101 (date))

and instead of using astronomical calculations for day_of_calendar, date - [start of calendar] + 1:

(cdate - date '1900-01-01') + 1 as day_of_calendar




But caution, sys_calendar doesn't calculate week numbers according to ISO standard, e.g. ISO week 2004W31 is from
Monday 2004-07-26 to Sunday 2004-08-01

Dieter