Subtracting two Julian Dates

5 pts.
Tags:
Oracle
Oracle development
I am very new to this position with limited knowledge. I would like to know how we can subtract two JULIAN dates in ORACLE. I have "ADMIT_DDT" and "DISCHARGE_DDT". Each of these dates consist of 10 digit number. I know first 5 numbers represent DATE and last five TIME. I would like to know to how how we can get result after subtraction as "Number of Day", "Number of Hours", "Number of Minutes" etc .. Please Help!

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

The Oracle datatype “INTERVAL DAY TO SECOND” stores (and displays) exactly the information you want.
If you have a time period expressed as a number of days (not necessarily an integer: 1.25 means 1 day and 6 hours, for example) you can use the NUMTODSINTERVAL function to convert that number to an INTERVAL DAY TO SECOND.
Getting the number of days between two dates in Oracle is simply a matter of subtracting them. This applies to any of the Oracle date-and-time datatypes, such as DATE or TIMESTAMP.
If your dates are stored in coded form in some other data type, you can use the TO_DATE function to create a true DATE from the coded data. (Storing dates in non-date-an-time data types is a bad idea for several reasons: the bother of converting is just one of them.)
Since TO_DATE operates on a string, and you’re starting with a number, you have to use TO_CHAR to format that number as a string.

So here’s what you have to do:
(1) Convert your coded dates to strings
(2) Convert those strings to DATEs
(3) Subtract the DATEs to get a difference (in days)
(4) Convert that number to an INTERVAL DAY TO SECOND

You didn’t say how your dates are coded. I’ll assume
— the first two digits are the number of years after 2000 (0-99)
— the next three digits are the day of the year (1-366)
— the last five digits are the number of seconds past midnight (0-86399)
So 734854652 means 14-Dec-2007 15:10:52. (03:10:52 PM)

Here’s what you can do:

<pre>
SELECT admit_dt
, discharge_dt
, NUMTODSINTERVAL (discharge_dt – admit_dt, ‘DAY’) — Steps (3) and (4)
AS dif
FROM ( — Begin in-line view to compute admit_dt, discharge_dt
SELECT table_x.*
, TO_DATE ( TO_CHAR (admit_ddt, ‘0000000000’) — Steps (1) and (2)
,’ YYDDDSSSSS’
) AS admit_dt
, TO_DATE ( TO_CHAR (discharge_ddt, ‘0000000000’) — Steps (1) and (2)
, ‘ YYDDDSSSSS’
) AS discharge_dt
FROM table_x
) — End in-line view to compute admit_dt, discharge_dt
</pre>
The output will look like this:
<pre>
ADMIT_DT DISCHARGE_DT DIF
——————– ——————– ——————————
31-Dec-2006 23:50:00 01-Jan-2007 00:10:00 +000000000 00:20:00.000000000
13-Dec-2007 14:10:52 14-Dec-2007 15:10:52 +000000001 01:00:00.000000000
</pre>
Use the SUBSTR or EXTRACT functions to format the dif column.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following