Julian date 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.

1. Find number 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. Simple Data security by encryption
Save all dob in Julian Date format in a number column. So non-tech savvy people cannot easily decode it

Update table set dob_number = to_char(dob,’J’);

Update table set dob = null;

dob_number is a char column
Note :- Examples are given for explanations only.

Did you find any other scenario ? Please comment.

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

1 thought on “Julian date Oracle”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s