CODEWITHSUNDEEP
oracle-sqldeveloper
Anderson Devin
Anderson Devin

Reputation: 5

To_Date, To_Char in oracle

My Query for on oracle DB is:

SELECT NBR, START_TIME,END_TIME, BYTES_DATA
FROM TABLE_NAME Partition (P201607)
WHERE BYTES_DATA <> 0 AND NBR LIKE '%29320319%'

and results in:

NBR         START_TIME          END_TIME            BYTES_DATA  
1029320319  2016-07-01 00:15:51 2016-07-01 00:22:44 158014048
1029320319  2016-07-01 00:22:51 2016-07-01 01:22:51 616324863   
1029320319  2016-07-01 01:22:51 2016-07-01 01:55:15 431354240   
1029320319  2016-07-01 01:55:22 2016-07-01 02:53:45 1040869155  
1029320319  2016-07-01 02:53:52 2016-07-01 03:53:52 40615861    
1029320319  2016-07-04 07:22:05 2016-07-04 07:22:05 4911
1029320319  2016-07-05 06:42:56 2016-07-05 07:42:56 58271774
1029320319  2016-07-05 07:42:56 2016-07-05 07:42:56 173
1029320319  2016-07-08 07:47:01 2016-07-08 07:47:01 105995

But I would like to filter these output based on Time. How can I get all records during this month(07) or last 7 days where the start_time and end_time is between 06:30:00 and 07:59:59?

Upvotes: 0

Views: 2271

Answers (1)

Nick Worthy
Nick Worthy

Reputation: 26

Using either to_date or to_char you can reformat the date/time field to match the required criteria, e.g.
This example will show all entries where the month in start or end time is same as the current month:

SELECT NBR, START_TIME,END_TIME, BYTES_DATA
FROM TABLE_NAME Partition (P201607)
WHERE BYTES_DATA <> 0 AND NBR LIKE '%29320319%'
AND ( (TO_DATE(START_TIME, 'mm') = TO_DATE(SYSDATE, 'mm') OR
      (TO_DATE(END_TIME, 'mm')   = TO_DATE(SYSDATE, 'mm'))

Change the matching criteria for last 7 days, e.g.
The trunc keyword removes the timestamp from a date/time value:

AND ( (TO_DATE(START_TIME, 'yyyy/mm/dd') >= (TRUNC(SYSDATE) - INTERVAL '7' days) OR
      (TO_DATE(END_TIME, 'yyyy/mm/dd')   >= (TRUNC(SYSDATE) - INTERVAL '7' days))

Change the format mask within the to_date to specify times, e.g.
This addition will filter for entries where start or end times are between 06:30:00 and 07:59:59 :

AND ( (TO_DATE(START_TIME,'hh24:mi:ss') BETWEEN 
       TO_DATE('06:30:00','hh24:mi:ss') AND 
       TO_DATE('07:59:59','hh24:mi:ss') ) OR 
      (TO_DATE(END_TIME,'hh24:mi:ss') BETWEEN
       TO_DATE('06:30:00','hh24:mi:ss') AND 
       TO_DATE('07:59:59','hh24:mi:ss') )

I like TechOnTheNet for descriptions/syntax of this and other Oracle functions. See link for full list of available format parameters.

Upvotes: 1

Related Questions