Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sun, 20 May 2002 @ 00:08:53 GMT


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


Subj:   Re: Testing for valid dates
 
From:   Dennis Calkins

Hi,

The dipcal.bteq script is the one that populates Sys_Calendar Database

You could copy this script and extend it beyond 2100 by adding additional inserts into the Year table....

INSERT INTO CALTMPYR VALUES(201);
INSERT INTO CALTMPYR VALUES(202);
     .
     .
     .

and changing the Database from Sys_Calendar to your own database name.

The Reason I suggest putting them into your own database is so you don't lose your updates when you upgrade.

However, the script assumes Jan 1, 1900 is calendar_day 0 and knows that it is a Monday so extending it the other way might be a little tougher because those 2 facts to are built into the views.

for example

You will see (((calendar_day + 0) mod 7) + 1) (day_of_week) and 1900 (base year) all over the views.

I think the + 0 is in the formula in case you pick a base year that doesn't start on a MONDAY.

You would have to update all of these locations to your new

   Base Year

and

   Base Day_of_week

Well isn't that amazing.

%cal 1 1900
   January 1900
 S  M Tu  W Th  F  S
    1  2  3  4  5  6
 7  8  9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

% cal 1 1800
   January 1800
 S  M Tu  W Th  F  S
          1  2  3  4
 5  6  7  8  9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

% cal 1 1700
   January 1700
 S  M Tu  W Th  F  S
    1  2  3  4  5  6
 7  8  9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

Jan 1, 1700 is also a Monday there it would be fairly simple to update the script to use 1700 as the base year

and then again mentioning The first 2 points

extend the number of rows inserted into the year table to at least 400 (1700 - 2100 )

change the Database name from Sys_Calendar to your own.



     
  <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