Display Query results based on last 24 hours 5

[nicks60610]

I am trying to query my database to show me data that was entered within the last 24 hours; this is what I have in the query, but I know it's not right because it errors out each time...

TO_DATE(EQ_EVENTS.EVENT_END_DATE, 'MM/DD/YY') = TO_DATE(SYSDATE-24, 'MM/DD/YY':hh24:Mi:ss)

Can anyone please show me in the right direction so I can learn more about this function and complete this query? I have been looking online, but I cannot find much regarding what I am trying to accomplish.

Thanks,

 

[ddiamond]

Do you get the same error if you run the following query?

select to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || EVENT_END_TIME
from EQ_EVENTS

If you do not get an error, can you post the queries output?

 

[ddiamond]

Again, I posted my last post before seeing your reply. If you decide you want to persue this further, let me know.

 

[nicks60610]

Sweet! This is what I got (I copied a section as there were thousands of records..)

03/30/2006 19:48
03/30/2006 20:11
03/31/2006 09:19
03/30/2006 12:28
03/30/2006 15:09
03/31/2006 11:17
03/30/2006 19:49
03/31/2006 07:48
03/31/2006 11:17
03/30/2006 20:28
03/31/2006 09:23
03/31/2006 08:05
03/30/2006 12:58
03/30/2006 19:50
03/30/2006 20:29
03/31/2006 09:23
03/31/2006 08:27
03/30/2006 12:59
03/30/2006 19:50
03/31/2006 00:17
03/31/2006 04:42
03/30/2006 13:00
03/30/2006 19:52
03/31/2006 04:37
03/30/2006 14:13
03/30/2006 19:53
03/31/2006 04:38

 

[ddiamond]

All of those date strings look fine to me. Here's the next experiment:

select to_date(to_char(EVENT_END_DATE, 'MM/DD/YYYY') || ' ' || 
EVENT_END_TIME, 'MM/DD/YYYY HH24:MI')
from EQ_EVENTS

 

[nicks60610]

Got this...

ORA-01843: not a valid month

 

[ddiamond]

This is very puzzling. There has to be some data that is causing the invalid month, but is not in the sample output you listed. The date portion of the string is coming from the to_char function which shouldn't be able to produce an invalid month. I tried entering an invalid time, but oracle gave me a different error message for that.

OK. We know the to_char function works for your data, but the to_date function does not. So lets re-do your query without using the to_date function. This is what I propose:

WHERE to_char(EVENT_END_DATE, 'YYYY/MM/DD') || ' ' || EVENT_END_TIME
  BETWEEN to_char(SYSDATE-1,'YYYY/MM/DD HH24:MI) 
      And to_char(SYSDATE  ,'YYYY/MM/DD HH24:MI)

I would suggest first add this to the simplified query. Notice I'm now putting the year first. This is so that string compares will work correctly.

 

[nicks60610]

Nope, now I get that "missing right paren..." error, so bizarre...

 

[ddiamond]

oops. I forgot single quotes after the MIs.

WHERE to_char(EVENT_END_DATE, 'YYYY/MM/DD') || ' ' || EVENT_END_TIME
  BETWEEN to_char(SYSDATE-1,'YYYY/MM/DD HH24:MI') 
      And to_char(SYSDATE  ,'YYYY/MM/DD HH24:MI')

 

[nicks60610]

Nice...that worked great! I will try this a bit further and incorporate more of the query...

 

[nicks60610]

You're a genius!! That works so good, unreal, WOW, I tell you what ddiamond, if you're ever in Portland, Maine, send me an email and I will take you out for some Maine lobster, wow, what a relief to have this working...thank you!

 

[ddiamond]

Thanks Nick. I'm glad it worked.

 

[Beilstwh]

I know why the original query said not a valid month, the table has nulls in some of the EVENT_END_DATE. The invalid month is the exact error they would get.

If you want to exclude the null records then

WHERE to_date(to_char(nvl(EVENT_END_DATE,sysdate+1), 'MM/DD/YYYY') || ' ' || nvl(EVENT_END_TIME,'00:00'), 'MM/DD/YYYY HH24:MI') BETWEEN SYSDATE-1 And SYSDATE

or always include them

WHERE to_date(to_char(nvl(EVENT_END_DATE,sysdate), 'MM/DD/YYYY') || ' ' || nvl(EVENT_END_TIME,'00:00'), 'MM/DD/YYYY HH24:MI') BETWEEN SYSDATE-1 And SYSDATE

Bill
Oracle DBA/Developer
New York State, USA

 

[ddiamond]

Thanks Bill. Another mystery solved.

 

[nicks60610]

Bill, sounds like you have experience with the tables I work with (Tabware), thanks for the tip!