Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 01 Sep 2004 @ 14:30:58 GMT


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


Subj:   Re: Generating week dates without sys_calendar
 
From:   Michael Larkins

Madhukar

The following is not intended to be the complete and end all solution to your question. However, it is intended to give you the basis to build what you need. The key to the solution is that Jan 1, 1900 was a Monday; your week starts on a Monday. The week of the year column is there like in the system calendar - a full week (ie week 1) starts on a Monday.

You can build the 7 dates by getting the Monday you are seeking and then selecting 6 more columns adding 1 to each respective date.

here is the SQL:

     sel  *
     from (sel case when ((date/10000*10000+101(date))-(101(date))) mod 7 > 0 then 0 else 1 end as Jan_week
                       ,((date/10000*10000+101(date))-(101(date))) mod 7 as day_num
                        ,case ((date/10000*10000+101(date))-(101(date))) mod 7 when 0 then 'Monday'
                                                                               when 1 then 'Tuesday'
                                                                               when 2 then 'Wednesday'
                                                                               when 3 then 'Thursday'
                                                                               when 4 then 'Friday'
                                                                               when 5 then 'Saturday'
                                                                                 else 'Sunday' end as Day_of_week
                                ,10*7+day_num as week10_days_of_yr
                                ,(1040101(date))+week10_days_of_yr as yr_date
     ) First_week_tbl

If you have any questions, please feel free to ask.


Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023