Selecting data via a time calculation

I searched and found a few solutions close to what I'm looking for, but nothing got me over the hump.

My SQL statement follows:

select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
from transaction0
where to_char(transaction0.document_date, 'mmddyy') = to_char(sysdate, 'mmddyy') and
log_field4 = 'O'
order by log_field3 desc

In english pseudocode, here is essentially what i'm looking for.

where log_field3 >= (Current Time - 60)
(All records from the past 60 minutes)

The time field (log_field3) follows the format: 0800, 1200, etc. So I believe its in the correct format to be compared to the systime.

Any help would be appreciated.
Thanks!
Kevin
kevinjdAsked:
Who is Participating?
 
ishandoCommented:
How about:

select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
from transaction0
where log_field4 = 'O'
and to_date(to_char(document_date, 'yyyymmdd') || log_field3, 'yyyymmddhh24mi') >= sysdate - 1/24
order by log_field3 desc;


0
 
kevinjdAuthor Commented:
I tried that, this was my result...

SQL> select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
  2  from transaction0
  3  where log_field4 = 'O'
  4  and to_date(to_char(document_date, 'yyyymmdd') || log_field3, 'yyyymmddhh24mi') >= sysdate - 1/24
  5  order by log_field3 desc;
and to_date(to_char(document_date, 'yyyymmdd') || log_field3, 'yyyymmddhh24mi') >= sysdate - 1/24
                                                *
ERROR at line 4:
ORA-01840: input value not long enough for date format
0
 
JankoratCommented:

The question is not clear but from what i understand you have table transaction0
with fields document_date with date(assumption) format and the time part is stored in another field log_field3 of type varchar2 . It is always better to store date in the date field including the time part for performance and for logic .

If this is  your situtation you could do the following

select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
from transaction0
where transaction0.document_date+substr( log_field3,1,2)/24+substr( log_field3,3,2)/1440   between sysdate  and sysdate-60/1440
and log_field4 = 'O'
order by log_field3 desc


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cjjcliffordCommented:
ishando's soln. should work...
can you post the output of:

DESCRIBE tranaction0

Also, if the field log_field3 can be NULL then ishando's answer may produce incorrect strings to be passed to the outer TO_DATE
This is because Oracle handles string concatentation of string and NULL in an "unexpected" way, returning the string, not NULL, e.g.
SELECT 'a' || null from dual;

so, if LOG_FIELD3 can be NULL, then the string being passed to the outer TO_DATE will not be formatted correctly....

to get around this, you need to wrap part of it in a DECODE:

select log_field1 Customer, document_number BOL_Number, log_field5 Purchase_Order, log_field3
from transaction0
where log_field4 = 'O'
and to_date(DECODE( log_field3, null, null, to_char(document_date, 'yyyymmdd') ) || log_field3, 'yyyymmddhh24mi') >= sysdate - 1/24
order by log_field3 desc;

Cheers,
C.
0
 
kevinjdAuthor Commented:
Jankorat,

What i'm looking for is all the records in which the time stored in log_field3 is within the past 60 minutes.
I'm currently working on testing your sql as well as cjjclifford.
0
 
ishandoCommented:
what datatype is the document_date?
if it is a date it should work. If not then it won't.
0
 
cjjcliffordCommented:
ishando, apart from when log_field3 is null, which would result in an incorrectly formatted string being passed to the outer TO_DATE()...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.