Archives of the TeradataForum

Message Posted: Thu, 02 Sep 2004 @ 17:16:35 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Thanks for all the suggestions. My requirements changed again, this time we need a week starting from Saturday and ending on Friday and I needed to get the week number and all week dates from today's date. From all of your suggestions, I have implemented it in this way. I welcome any more suggestions on this.

I am calculating previous week number and dates from today's' date. "DAYOFWEEK" is day of week from Monday, which I am calculating in UNIX and substituting with a number before sending it to teradata.

     DAYOFWEEK=`date +%u`
     #define DAYOFWEEK $DAYOFWEEK;
     .runfile .........

     select
     (case when

     (day_of_year - ( 6- ((calendar_date - day_of_year+1) - ('1900-01-01'
     (date) ) ) mod 7))<0 then 0

     else

     (day_of_year - (6-((calendar_date - day_of_year+1)- ('1900-01-01'
     (date))) mod 7 ))/7+1

     end) week_number,
     day_of_year,
     (
     case when day_of_week = 7 then 1
     else day_of_week+1
     end
     ) day_of_week,
     calendar_date
     from sys_calendar.calendar
     where
     calendar_date >=  date  - DAYOFWEEK - 8
     and
     calendar_date <  date -1 - DAYOFWEEK
     ;

	<Prev	Next>		<<First	<Prev	Next>	Last>>