Subtracting two Julian Dates
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!
Software/Hardware used:
Software/Hardware used:
ASKED:
December 14, 2007 7:46 PM
UPDATED:
December 14, 2007 9:19 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
