Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, oracle.

Asked: September 16, 2001 - 10:56 pm UTC

Last updated: February 17, 2005 - 10:09 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Tom

I was going through some of the packages written by the developers who wrote the application before me.

I see that they first convert a date passed into a procedure or function as input parameter , into the julian format and then work onit. What is the specific purpose.

In what circumstances a date is converted into Julian format.

What are the specific uses of a julian format date.

Thank you

and Tom said...

I cannot tell you why they do that, you would have to ask them. A julian date is the number of days from a fixed point in time.


1* select to_char( sysdate, 'j' ), to_char( to_date( 1, 'j' ), 'dd-mon-yyyy
AD' ) from dual
ops$tkyte@8.0> /

TO_CHAR TO_CHAR(TO_DAT
------- --------------
2452166 01-jan-4712 BC

so, its just the number of days since jan 1, 4712 BC.

Maybe they did it as a long winded way to truncate the time component:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select to_char( to_date( to_char(sysdate,'j'), 'j' ), 'dd-mon-yyyy hh24:mi:ss' ) from dual;

TO_CHAR(TO_DATE(TO_C
--------------------
17-sep-2001 00:00:00

if so, trunc(date) would be easier.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Different ways to truncate the time portion of a date

A reader, September 17, 2001 - 2:09 pm UTC

So is Julien date used to truncate date component. Or rather, does the Julien date does not contain component.

can you tell me different ways to truncate the time portion of a date. In oracle 9i do we have a separate datatype for time.

Where can I get a document listing 9i new features, sql, plsql, and database

Tom Kyte
September 17, 2001 - 4:30 pm UTC

A julian date is not used to truncate a time component of a date -- I was hypothesizing that maybe that is what is might have been used for.

TRUNC is best for that.

See
</code> http://www.oracle.com/pls/db901/db901.to_toc?pathname=index.htm <code>
for 9i docs

Example

A reader, September 17, 2001 - 5:27 pm UTC

Please give an example when a julian format is an absolute necessity.

Further, which is inexpensive and why

Truncing time using julian dagte
Truncing date suing trunc




Tom Kyte
September 17, 2001 - 8:17 pm UTC

It is never an absolute necessity. It is a method of storing the date. It is no more or less functional then anything else. It is an option.

trunc(dt) will be more efficient then to_date( to_char( dt, 'j' ), 'j' )

Date other than Jan 1, 4712 BC

VA, February 17, 2005 - 9:01 am UTC

How can I use some "base" date other than Jan 1, 4712 BC when working with Julian dates?

Thanks

Tom Kyte
February 17, 2005 - 10:09 am UTC

addition and subtraction will work if you want to make up your own julian date.

Say you wanted it to start "one day later".

Add one to our julian date.

Just what I needed

Jodi, April 29, 2005 - 1:53 pm UTC

Just needed to get sysdate in julian format - this was perfect! Thanks.

when Julian <> Julian

Duke Ganote, July 14, 2005 - 7:12 pm UTC 

There are two definitions of Julian date.  Oracle has implemented the formal, astronomical definition.  

An informal usage is the concatenation of the year and the number of days since the beginning of the year; see mention of at
http://tycho.usno.navy.mil/mjd.html 
which decries the latter usage as "erroneous".  However, I note the military's own "UMMIPS" requistion system uses the latter definition, calling this a "Julian" format in much of the documentation:

SQL> select sysdate, to_char(sysdate,'YDDD') "YDDD" from dual;

SYSDATE   YDDD
--------- ----
14-JUL-05 5195

Julian <> Julian redux

Duke Ganote, July 14, 2005 - 7:27 pm UTC

I found Excel documentation that says: "Julian date" is sometimes used to refer to a date format that is a combination of the current year, and the number of days since the beginning of the year.

But it's not unusual to trip over that... I've done it, too! ;)
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8990759736006 <code>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library