Tech Tip: Extend the date range in DAY_DIM table

Document created by AdrianJohnsonUK Employee on Feb 16, 2017Last modified by AdrianJohnsonUK Employee on Feb 16, 2017
Version 2Show Document
  • View in full screen mode

Looking to the future, the current contents of the DAY_DIM table in Data Manager database range from 1990-01-01 to 2021-01-01.  For end users loading / creating 24 month forward-looking forecasts, that end date is starting to edge closer.  Better to take preventative measures early (after all, we're in the business of being proactive ).


The attached SQL scripts can be executed to:

   1) Check the current date range in DAY_DIM

   2) Extend it by 10 years to 2031-01-01

   3) Optional - Check the new date range in DAY_DIM after running the second script


They can be run via SQLPlus or SQL Developer (other SQL IDEs are available) as the environment allows.


Important: Business Hour Windows and 'Day of the Week'

One small but important point to consider for non-US installations is that Oracle defines a different 'first day of the week' in different locales - in the US, it is Sunday, in most of Europe it is Monday for example - if using the Business Hours functionality, 'Day of the Week' is an important consideration.

Since the install requirements have America-American as the required locale, and the Internationalization Tech Note from CA Services also recommends setting the Oracle session settings to America-American with a logon trigger, the attached Extend_Day_Dim_DateRange.sql script also sets the session locale to America-American to improve consistency.

If there is any concern, run the Check_Day_Dim_DateRange.sql script and check the Day_Of_Week and Day_Num_in_Week columns for consistency - modify the script as appropriate if needed.