January 25, 2013 at 12:58 pm
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.
Any ideas?
Thanks,
jess.
This is a sample result set:
TIME_CLANEITEMSDATE_C BUCUSTBAL
05:00:001111/18/2012123411.59
10:00:001211/18/2012123411.09
22:00:001311/18/2012123411.42
This is my code:
SELECT
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,
LANE,
DTL_TY AS ITEMS,
DATE_C,
BU,
COUNT(DATE_C) AS CUST,
SUM(BAL_AMT) AS BAL
FROM
(
SELECT
DTL_QTY,
DATE_C,
BU,
BAL_AMT,
LANE,
TIME_C
FROM TRANS_C
WHERE
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')
)
GROUP BY
TIME_C,
LANE,
DTL_TY,
DATE_C,
BU
ORDER BY BU, DATE_C, LANE
January 25, 2013 at 4:50 pm
Could you please post the schema of the table? I'm curious about the datatype of date_c and time_c columns.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply