• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

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
0
kevinjd
Asked:
kevinjd
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now