1) Is it possible to specify BC(Before Christ) or AD in date format ?. I checked the Manual , but i couldn't get anything. 2) Is there any equivalent syntax in Teradata to find the number of days since Jan 1 ,4712 BC.
Valid range of dates in Teradata is from 0001-01-01 through 9999-12-31. The same applies for timestamps as well. To work with Julian dates you may need to build a physical table or UDF and use a character data type in your table(s).
BTW, here is my solution: change the sys_calendar caldates table by adding the precomputed value in, expand the calbasics view , calendar view with the column. it should save a lot of headache system wide for all applications using such date. It also saves cpu seconds considering the date time logic were constantly used in typical data warehouse. a tiny storage exchange for savings of CPU.