I have a query that pulls back both a Time and a Date column. I'm trying to figure out how I can search by the date and then by the time.
Currently, I have a statement to search by the date but when I add a time statement, no results are pulled back and I know they should be there.
Idealy, an example i'd like to see are all results on the 18th from 05:00 until 17:00 that day.
This is a sample result set:
TIME_C LANE ITEMS DATE_C BU CUST BAL
05:00:00 1 1 11/18/2012 1234 1 1.59
10:00:00 1 2 11/18/2012 1234 1 1.09
22:00:00 1 3 11/18/2012 1234 1 1.42
This is my code:
TO_CHAR(TO_DATE(CASE WHEN TO_NUMBER(TO_CHAR(TIME_C, 'mi')) > 59 THEN TO_CHAR(TRUNC(TIME_C, 'hh') + 1 / 48, 'hh24:mi:ss')ELSE TO_CHAR(TRUNC(TIME_C, 'hh'), 'hh24:mi:ss') END,'hh24:mi:ss'),'hh24:mi:ss') AS TIME_C,
DTL_TY AS ITEMS,
COUNT(DATE_C) AS CUST,
SUM(BAL_AMT) AS BAL
BU IN (329)
AND LANE < 30
AND DATE_C BETWEEN TO_DATE ('11/18/2012','mm/dd/yyyy') AND TO_DATE ('11/24/2012','mm/dd/yyyy')
ORDER BY BU, DATE_C, LANE