CODEWITHSUNDEEP
sqloracle-databasedatehour
Angabo
Angabo

Reputation: 129

How can i get the hours that are between a certain fringe oracle

I need to get the hours that are between a time slot. My code is as follows, but I do not get exactly the results I need. Since it does not show me all the results that are in that range

SELECT
    *
FROM
    (
        SELECT DISTINCT
            TO_DATE(TO_CHAR(date_quote,'dd/MM/yy'),'dd/MM/yy') date_quote,
            TO_DATE(TO_CHAR(start_time,'dd/MM/yyyy HH24:MI:SS'),'dd/MM/yyyy HH24:MI:SS') start_time,
            TO_DATE(TO_CHAR(end_time,'dd/MM/yyyy HH24:MI:SS'),'dd/MM/yyyy HH24:MI:SS') end_time

        FROM
            quote
        WHERE 
                start_time < to_timestamp('1970/01/01 11:30:00','yyyy/MM/dd HH24:MI:SS')
            and
                end_time > to_timestamp('1970/01/01 11:15:00','yyyy/MM/dd HH24:MI:SS') 

        AND
            date_quote= TO_CHAR(TO_DATE('2017-05-01','yyyy-MM-dd'),'dd/MM/yyyy')
    )
ORDER BY
    date_quote ASC,
    start_time ASC;

I have also tried with >= <= but it does not give me the expected result either

Upvotes: 0

Views: 65

Answers (2)

Akanksha
Akanksha

Reputation: 101

It seems you want to calculate the total time taken to close a task. so you can use

SELECT *
FROM
  (SELECT 
    TO_CHAR(TO_DATE(TO_CHAR(end_time,'dd/MM/yyyy HH24:MI:SS'),'dd/MM/yyyy HH24:MI:SS') - TO_DATE(TO_CHAR(start_time,'dd/MM/yyyy HH24:MI:SS'),'dd/MM/yyyy HH24:MI:SS'),'HH24:MI:SS') total_time_taken
  FROM quote
  WHERE start_time < to_timestamp('1970/01/01 11:30:00','yyyy/MM/dd HH24:MI:SS')
  AND end_time     > to_timestamp('1970/01/01 11:15:00','yyyy/MM/dd HH24:MI:SS')--- kinldy check your condition as end time is less then task start time
  AND date_quote   = TO_CHAR(TO_DATE('2017-05-01','yyyy-MM-dd'),'dd/MM/yyyy')
  )
ORDER BY date_quote ASC,
  start_time ASC;

Upvotes: 1

MT0
MT0

Reputation: 168351

Assuming date_quote, fecha_apunte_fh, start_time and end_time are all DATE or TIMESTAMP columns and that start_time is before end_time then

SELECT *
FROM   (
  SELECT DISTINCT
         TRUNC( date_quote ) date_quote,
         start_time,
         end_time
  FROM   quote
  WHERE  start_time > TIMESTAMP '1970-01-01 11:15:00'
  AND    end_time   < TIMESTAMP '1970-01-01 11:30:00'
  AND    fecha_apunte_fh = DATE '2017-05-01'
)
ORDER BY
    date_quote ASC,
    start_time ASC;

Upvotes: 0

Related Questions