Oracle SQL Date doesn't show time? How to show time between two dates?

Question

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?

Thanks!

Have you tried searching? this question seems to be a duplicate of ( among others) [this](http://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql9) — Aleksej, Apr 08 '16 at 08:18

score 2 · Answer 1 · edited May 23 '17 at 12:07

2

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;

edited May 23 '17 at 12:07

Community

1
1

answered Apr 08 '16 at 08:22

MT0

143,790
11
59
117

Cool I didn't know that, however how am I gonna calculate time difference between two datetimes? TODATE(TO_CHAR)? – SJ19 Apr 08 '16 at 11:42
@SJacobs see dcieslak's [answer](http://stackoverflow.com/a/36494839/1509264) or [this question](http://stackoverflow.com/q/1096853/1509264) or [this question](http://stackoverflow.com/questions/7943055/oracle-date-difference-to-get-number-of-years). – MT0 Apr 08 '16 at 11:57

score 0 · Answer 2 · answered Apr 08 '16 at 08:21

   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;

Will produce:

    time_difference
   -------------
    121 Hours 7 Minutes 59 Secs

Oracle SQL Date doesn't show time? How to show time between two dates?

2 Answers2