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:
, NUMTODSINTERVAL (discharge_dt – admit_dt, ‘DAY’) – Steps (3) and (4)
FROM ( – Begin in-line view to compute admit_dt, discharge_dt
, TO_DATE ( TO_CHAR (admit_ddt, ’0000000000′) – Steps (1) and (2)
) AS admit_dt
, TO_DATE ( TO_CHAR (discharge_ddt, ’0000000000′) – Steps (1) and (2)
, ‘ YYDDDSSSSS’
) AS discharge_dt
) – End in-line view to compute admit_dt, discharge_dt
The output will look like this:
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
Use the SUBSTR or EXTRACT functions to format the dif column.