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?

Thanks!

Answers


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;

Will produce:

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

Need Your Help

Where does a rails (3) application define what is supposed to happen in activesupport-3.0.0.rc/lib/active_support/testing/setup_and_teardown.rb?

ruby-on-rails ruby unit-testing activerecord

I generated a model file but then chose to let it single table inherit from another model. I had to run no migration, because the columns all were already there.

Read all files, change content, save again

powershell

I'm trying to do a replace in content of all files in a certain directory structure.