How do I convert EVENT_DATE_B - EVENT_DATE_A
which is a number of days to string with HH:MM
format?
Use TO_CHAR to display it in any format you like. For example: SELECT TO_CHAR ( TO_DATE (date_value, 'yyyy-mm-dd') , 'mm/dd/yyyy' ) FROM table_x; Things are much easier if you store dates in DATE columns.
To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .
Another approach (one query can be on different days):
with tt as (
select numToDsinterval((EVENT_DATE_B - EVENT_DATE_A ), 'DAY') dsint
from t)
select (extract(day from dsint)*24)+extract(hour from dsint) ||
':' ||extract(minute from dsint)
from tt
Here is a sqlfiddle demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With