Generate all possible dates from 1900-01-01 to 9999-12-31

Database
Enthusiast

Generate all possible dates from 1900-01-01 to 9999-12-31

Hi,

 

Currently SYS_CALENDAR.CALENDAR provides dates from 1900-0-01 to 2100-12-31.

I want to generate all possible dates from 1900-01-01 to 9999-12-31.

 

Is there a way we can create a view which will generate all dates as required ?

 

 

3 REPLIES
Senior Apprentice

Re: Generate all possible dates from 1900-01-01 to 9999-12-31

You can utilize EXPAND ON to create a range of dates:

SELECT End(pd) AS calendar_date
FROM dbc.dbcinfov
WHERE InfoKey = 'VERSION'
EXPAND ON PERIOD(DATE '1900-01-01' -1, DATE '9999-12-31') AS pd BY ANCHOR PERIOD DAY
;

 

 

Enthusiast

Re: Generate all possible dates from 1900-01-01 to 9999-12-31

Hi Dieter,

 

Thank you for replying.

My intention is to create a view on top of system calander with UNION of code to generate dates from 2101-01-01 till 9999-12-31.

Is there any other way we can do this ?

Tags (1)
Teradata Employee

Re: Generate all possible dates from 1900-01-01 to 9999-12-31

I am not sure if this is what you want, but you can create a view that looks like any of the Sys_Calendar views by creating your own CALDATES table and cloning the Sys_Calendar view you want to use, just pointing it to your new table.  The CALDATES are just a list of dates.  You can create your own list in a file and load that file to your new dates table.  (Doing this in SQL or SPL would be too slow because date generation is a serial process.)  Create the file with an awk or (or C or python, whatever) program that iterates over the function below.  This function converts a number ("Julian Day Number") to a calendar date.  The Julian date for 01-JAN-1900 is 2415021.  Start there and loop through, incrementing by 1 until you reach 5373484 (31-DEC-9999).  Here is the gawk code for that function:

 

function jdnl_to_ymd(jdn ,LASTJULJDN,daysPer400Years,x,z,m,d,y,yr) {
	LASTJULJDN = 2299160;	# jdn of Pope Gregory XIII's decree, 10/04/1582
	daysPer400Years = 146097;
	julian = (jdn <= LASTJULJDN ? 1 : 0);
	x = jdn + 68569;
	if (julian) {
		x += 38;
		daysPer400Years = 146100;
	}
	z = int( 4 * x / daysPer400Years );
	x = x - int( (daysPer400Years * z + 3) / 4 );
	y = int( 4000 * (x+1) / 1461001 );
	x = int( x - 1461 * y / 4 + 31.75 );	# awk fix, instead of 31 );
	m = int( 80 * x / 2447 );
	d = x - int( 2447 * m / 80 );
	x = int( m/11 );
	m = m + 2 - 12 * x;
	y = 100 * (z - 49) + y + x;
	yr  = y > 0 ? y : y-1;		# adjust BC years
	mo  = m;
	day = d;
	return yr " " mo " " day;
}

(Note: a language other than awk may require changing 31.75 above to 31, depending on how the arithmetic is processed.)