Query Using Sysdate-1 1

[cmt]

I am attempting to write a query which will be run daily to capture data from the previous day. What I have so far is:

SELECT a.name, a.username, a.application_number, a.position_cd,
a.start_dt_tm,
a.end_dt_tm,
a.person_id
from application_context a
where
a.START_DT_TM > any (select sysdate -2 from dual)
and a.start_dt_tm < (select sysdate -1 from dual)

This does not work because it uses the current time when it runs. How can I say &quot;yesterday's date from 0000 to 2400&quot;?

 

[THoey]

For Oracle, I use a SUBSTR(MyDate, 1, 10) = SYSDATE - 1. I thought someone used a TRUNCATE command once, but that may have been specifically for MS Access. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[sem]

Your query may be smth like:

SELECT a.name, a.username, a.application_number, a.position_cd,
a.start_dt_tm,
a.end_dt_tm,
a.person_id
from application_context a
where
a.START_DT_TM
between trunc(sysdate-2) and trunc(sysdate-1)

 

[Maugo]

Hi !

Use

SELECT a.name, a.username, a.application_number, a.position_cd,
a.start_dt_tm,
a.end_dt_tm,
a.person_id
from application_context a
where
( a.START_DT_TM >= trunc(sysdate-1) AND
a.START_DT_TM <= trunc(sysdate-1) +(1/86400*86399) )

Now Oracle can also use an Index on application_context.START_DT_TM.
&quot;(1/86400*86399)&quot; adds 86399 seconds to 00:00.
This is 23:59:59.

 

[MikeLacey]

Try this:

Where Trunc(A.START_DT_TM) = Trunc(SysDate-1)

Maugo's solution would be faster though, if there are many rows to search, becuase it will let Oracle make use of any indexes.

If the table contains only a few hundred rows it probably won't gain much from the use of an index. Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[newToOracle]

On the same note, how would you format an sql to find all records where the last_fail_time which is a DATE field is less then the current sysdate?

select *
from foo.bar
where foo.last_fail_time < ???????;

 

[newToOracle]

Correction to first posted message. Please ignore that one.
On the same note, how would you format an sql to find all records
where the last_fail_time which is a DATE field is 5 minutes less then the current sysdate?

select *
from foo.bar
where foo.last_fail_time < ???????;

 

[THoey]

I think I am misunderstanding you, because this answer is almost exactly what you had. Also, it seems to me that ALL last_fail_times are going to be less than the sysdate, since you are tracking events that happened in the past and not events that are going to happen in the future (Mind Reader?)

SELECT *
FROM foo
WHERE TRUNC(foo.last_fail_time) < SYSDATE;

See if it helps, if not, please post some more details.
Terry M. Hoey

 

[newToOracle]

Thanks for replying. What I meant is to find all records that failed 5 minutes prior to the current sysdate?

 

[THoey]

See, not only did I misunderstand, I can't read.

SELECT *
FROM foo
WHERE to_char(foo.last_fail_time, 'DD-MON-YY HH:MI:SS') >
to_char((SYSDATE - .003472), 'DD-MON-YY HH:MI:SS') ;

It works out to being SYSDATE (today) - 1 (DAY) divided by number of minutes in a day (1440) times 5 for 5 minutes

I think that should work... Terry M. Hoey

 

[sem]

I think that these to_char conversions are a bit redundant, for they may prevent from utilizing index if any. They also may fail due to that 'APR'< 'JAN' while comparing them as strings.

SELECT *
FROM foo
WHERE foo.last_fail_time > sysdate - 5/24/60

 

[dgo]

trunc(sysdate) gives a time of 00:00