asked on

SQL query not to SELECT something for a period of time

I've an sql query which SELECTs certain fields from a table if the (sysdate - upd_dt) * 1440 > 10
basically, this query is a perl script which gets executed every 5 mins
Now, I need to modify the query so that it doesn't selects anything if the system time is between 0000 and 0115

I dont want to decrease the frequency of the perl script as it does some other stuffs.

Is there any way to perform this?

rgds
prasen

prasen120998

ASKER

I'm using Oracle database

ASKER CERTIFIED SOLUTION

BillAn1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

annamalai77

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

BillAb1 and annamalai77 are on the good way, so give points to them.
But they should read more carefully, because the question is in the negative way, thus add a NOT to the condition:

SELECT *
FROM MYTABLE
WHERE (sysdate - upd_dt) * 1440 > 10
AND NOT TO_CHAR(sysdate, 'HHMI') between '0000' and '0115'

respectively:
ELECT * FROM MYTABLE
WHERE (sysdate - upd_dt) * 1440 > 10
AND TO_CHAR('HHMI', sysdate) > '0115'

Cheers

annamalai77

hi the correct query

a minor correction as said by angel

SELECT *
FROM MYTABLE
WHERE (sysdate - upd_dt) * 1440 > 10
AND to_number(TO_CHAR(sysdate, 'HHMI')) not between 0000 and 0115

regards
annamalai

Guy Hengel [angelIII / a3]

Hi annamalai77,
may you tell us why you use to_number?

annamalai77

hi angel

just to convert the character data to numeric data thats all. even without to_number the query will work but to avoid any problems which might arise i have used to_number.

regards
annamalai

prasen120998

ASKER

Thanks for the suggestions ---
do I need to change the below
to_number(TO_CHAR(sysdate, 'HHMI'))

to
to_number(TO_CHAR(sysdate, 'HH24MI'))
in order to get the 24 hrs clock?

annamalai77

hi

yes u can use if u want to convert it into a 24hr clock.

regards
annamalai

Guy Hengel [angelIII / a3]

As I pointed out previously:
>>BillAb1 and annamalai77 are on the good way, so give points to them.
CHeers