Oracle SQL Date doesn't show time? How to show time between two dates?
I want to calculate time between two dates, to show how much time there's left in a Sale. However when I display dates, it only shows the date and not the time (though I inserted date with dd/mm/yyyy hh24:mi:ss).
SELECT date FROM Sale
This is the Sale entity. (left unrelevant attributes out)
CREATE TABLE Sale ( dateStart DATE NOT NULL, dateEnd DATE NOT NULL );
Basically, I just want to show the hours, minutes, seconds left till the sale is over. So dateEnd minus the dateStart... How would I go about this?
Dates do not have a format - they are represented internally by 7 or 8 bytes.
It is the SQL client that formats the date and by default SQL plus will use the NLS_DATE_FORMAT session parameter (this is a client variable not one that is set globally).
If you want a consistently formatted date then you will need to convert it to a string
SELECT TO_CHAR( datestart, 'dd/mm/yyyy hh24:mi:ss` ) FROM sale;
with dates as ( select to_date('20160401 11:21','yyyymmdd hh24:mi') as date1, to_date('20160327 10:13','yyyymmdd hh24:mi') as date2 from dual ) SELECT floor((date1-date2)*24) || ' Hours ' || mod(floor((date1-date2)*24*60),60) || ' Minutes ' || mod(floor((date1-date2)*24*60*60),60) || ' Secs ' time_difference FROM dates;
time_difference ------------- 121 Hours 7 Minutes 59 Secs