How do I search by DATE then TIME in Oracle?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Working with Oracle » How do I search by DATE then TIME in Oracle?

How do I search by DATE then TIME in Oracle?

Rate Topic

Display Mode

Topic Options

Author

Message

jess.

Posted Friday, January 25, 2013 12:58 PM

Grasshopper

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

PaulB-TheOneAndOnly

Posted Friday, January 25, 2013 4:50 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 2,979, Visits: 4,390

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 »

Permissions