Julian date in Oracle

Julian days are the number of days since January 1, 4712 BC. It is represents as a number. So every date since January 1, 4712 BC can be represented as a number, which is called Julian Date.

Examples:-

SQL > select to_char(sysdate,’J’) as julian from dual; 

JULIAN
——-
2456317

SQL > select to_char(to_date(’24-Jan-2013′,’dd-mon-yyyy’),’J’) as julian from dual;

JULIAN
——-
2456317

SQL > select to_char(to_date(’01-Jan-4712 BC’,’dd-mon-yyyy AD’),’J’) as julian from dual;

JULIAN
——-
0000001

So Julian date starts on 01-Jan-4712 BC.

Note :- ‘J’ is the format string to convert date to Julian Date.

Convert Julian Date to date

‘JSP’ format string converts Julian Date to date.

Examples :-

SQL > select to_char(to_date(2456317,’JSP’),’dd-Mon-yyyy’) as day  from dual;

DAY
————
24-Jan-2013

SQL > select to_char(to_date(1,’JSP’),’dd-Mon-yyyy AD’) as day  from dual;

DAY
————–
01-Jan-4712 BC

Julian Date Usage

There are few situations Julian Date become handy. I have stated few examples. remember now.

1) Find no. of days between two date

Below query finds days between 29-Mar-2011 and 01-Jan-1980.

select
to_char(to_date(’01-Jan-1980′,’dd-mon-yyyy’),’J’) as start,
to_char(to_date(’29-Mar-2011′,’dd-mon-yyyy’),’J’) as end,
(
to_char(to_date(’29-Mar-2011′,’dd-mon-yyyy’),’J’)
–
to_char(to_date(’01-Jan-1980′,’dd-mon-yyyy’),’J’)
) as difference
from dual;

START   END      DIFFERENCE
——- ——- ———-
2444240 2455650      11410

2. File name generation based on date.
Assume marketing department sending the daily files to IT department in the format of julian date.
So I would say it is quite easy to interpret as a number though it as a date internally.
Say, “Sales_2456317.txt” may be a file generated on 24-Jan-2013 and so so.

3. You can use it for simple encryption.
I can save all dob in Julian Date format in a number column. So non-tech savvy people cannot easily decode it.
Note :- The above examples are given just to explain the possible usages. This need not be appropriate in some circumstances.