CODEWITHSUNDEEP
sqloracle-database
radha
radha

Reputation: 175

How to find minutes from Date & Time in Oracle?

I have 4 columns in my table RUNNING_INFO

TRAIN_START_DATE  VARCHAR2(8)
SCHEDULED_DATE VARCHAR2(8)
ACTUAL_DATE    VARCHAR2(8)
SCHEDULED_TIME VARCHAR2(8)
ACTUAL_TIME    VARCHAR2(8)

I have to extract the minutes from Actual/Scheduled Date & time , I have written following code for this

SELECT TRAIN_START_DATE,SCHEDULED_DATE,ACTUAL_DATE,SCHEDULED_TIME,ACTUAL_TIME,
CEIL((TO_DATE(concat(ACTUAL_DATE, ACTUAL_TIME) , 'DD-Mon-YYYYHH24:MI:SS') - TO_DATE(TRAIN_START_DATE, 'DD-Mon-YYYY'))*1440) as ACTUAL_MINS ,
CEIL((TO_DATE(concat(SCHEDULED_DATE, SCHEDULED_TIME) , 'DD-Mon-YYYYHH24:MI:SS') - TO_DATE(TRAIN_START_DATE, 'DD-Mon-YYYY'))*1440) as SCH_MINS FROM RUNNING_INFO

But I am not getting correct values in ACTUAL_MINS and SCH_MINS , for instance for the following rowset of Output , values are coming incorrectly in minutes : Please guide how to find minutes correctly .

enter image description here

Upvotes: 0

Views: 75

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11616

minutes between two dates where those dates include the time is

( date1 - date2 ) * 1440

in your case, where the date/time is separated into two columns, you can merge them become a date, so something like

to_date(to_char(sched_date,'yyyymmdd')||sched_time,'yyyymmddhh24:mi:ss')

for each date before doing the subtraction. And then you do not need CEIL because floating point will round up even the tiniest decimal

SQL> with t as
  2  (
  3  select
  4    '04-sep-2020' x1,
  5    '22:02:00' x2,
  6    '05-sep-2020' x3,
  7    '00:08:00'  x4
  8  from dual
  9  )
 10  SELECT
 11  CEIL((TO_DATE(concat(x1, x2) , 'DD-Mon-YYYYHH24:MI:SS') - TO_DATE(x3, 'DD-Mon-YYYY'))*1440) as ACTUAL_MINS ,
 12  CEIL((TO_DATE(concat(x3, x4) , 'DD-Mon-YYYYHH24:MI:SS') - TO_DATE(x1, 'DD-Mon-YYYY'))*1440) as SCH_MINS
 13  FROM t;

ACTUAL_MINS   SCH_MINS
----------- ----------
       -118       1449

SQL>
SQL> with t as
  2  (
  3  select
  4    '04-sep-2020' x1,
  5    '22:02:00' x2,
  6    '05-sep-2020' x3,
  7    '00:08:00'  x4
  8  from dual
  9  )
 10  SELECT
 11  (TO_DATE(concat(x1, x2) , 'DD-Mon-YYYYHH24:MI:SS') - TO_DATE(x3, 'DD-Mon-YYYY'))*1440 as ACTUAL_MINS ,
 12  (TO_DATE(concat(x3, x4) , 'DD-Mon-YYYYHH24:MI:SS') - TO_DATE(x1, 'DD-Mon-YYYY'))*1440 as SCH_MINS
 13  FROM t;

ACTUAL_MINS   SCH_MINS
----------- ----------
       -118       1448

SQL>

Upvotes: 1

Related Questions