Julian day

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--&gtnormal 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 --&gt 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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26719/viewspace-1050142/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26719/viewspace-1050142/