Get sales between hours interval-CodePudding

I am trying to get sales between 7:00 and 10:00. I am new in SQL (Oracle) and I am struggle with this query. So far I have the following, but I get 'inconsistent data type, TIME is expected,

SELECT s.loc_code, SUM (sales_at_net_retail_price) AS "Sales"
    FROM dw_star.dw_fact_sales_line s
         INNER JOIN dw_star.dw_dim_transactions tr
             ON s.TRANS_SRC_ID = tr.TRANS_SRC_ID
   WHERE     s.calendar_date = TRUNC (SYSDATE) - 1
         AND s.loc_code = 2020
         AND (CAST (trans_time AS TIME) >= '07:00:00')
         AND (CAST (trans_time AS TIME) <= '10:00:00')
GROUP BY s.loc_code

trans_time column has the following format: 6/2/2022 9:03:45 pm

Could you please help me with this?

CodePudding user response：

You are comparing the time value with a string value. You can rather use EXTRACT function to fetch the hour from that column and then compare that value.

SELECT s.loc_code, SUM (sales_at_net_retail_price) AS "Sales"
  FROM dw_star.dw_fact_sales_line s
       INNER JOIN dw_star.dw_dim_transactions tr
                  ON s.TRANS_SRC_ID = tr.TRANS_SRC_ID
 WHERE s.calendar_date = TRUNC (SYSDATE) - 1
   AND s.loc_code = 2020
   AND TO_CHAR(trans_time, 'HH') >= '07'
   AND TO_CHAR(trans_time, 'HH') <= '10'
 GROUP BY s.loc_code