Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I search by DATE then TIME in Oracle? Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 12:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:17 AM
Points: 14, Visits: 41
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_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:

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
Post #1411905
Posted Friday, January 25, 2013 4:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1411958
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse