our date dimension surrogate key is built by Julian day. (Julian day: the number of days since January 1, 4712 BC. ) The following are some operations on that:

1.Julian date-->normal oracle date:

SQL> select date_sk,to_date(date_sk,'j') normal_date from dim_date
2 where date_sk<>0 and rownum<10;

DATE_SK NORMAL_DA
---------- ---------
2451545 01/JAN/00
2451546 02/JAN/00
2451547 03/JAN/00
2451548 04/JAN/00
2451549 05/JAN/00
2451550 06/JAN/00
2451551 07/JAN/00
2451552 08/JAN/00
2451553 09/JAN/00

2.normal oracle date --> Julian date

SQL> select date_sk,to_char(calendar_date,'J'),calendar_date from vault.dim_date
2 where date_sk<>0 and rownum<10;

DATE_SK TO_CHAR CALENDAR_
---------- ------- ---------
2451545 2451545 01/JAN/00
2451546 2451546 02/JAN/00
2451547 2451547 03/JAN/00
2451548 2451548 04/JAN/00
2451549 2451549 05/JAN/00
2451550 2451550 06/JAN/00
2451551 2451551 07/JAN/00
2451552 2451552 08/JAN/00
2451553 2451553 09/JAN/00