OraFAQ Forum: SQL & PL/SQL » check if system time falls between start_time and end

Home » SQL & PL/SQL » SQL & PL/SQL » check if system time falls between start_time and end_time (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

check if system time falls between start_time and end_time (merged) [message #391104]

Wed, 11 March 2009 01:15

laknar
Messages: 30
Registered: February 2009

Member

SQL> create table test(start_time date,grace_time number);

Table created.

SQL> insert into test values(sysdate,15);

1 row created.

if the system time falls between start_time and (start_time+grace_time)

do some DML statements

[Updated on: Wed, 11 March 2009 01:56] by Moderator

Report message to a moderator

Re: check if system time falls between start_time and end_time [message #391106 is a reply to message #391104]

Wed, 11 March 2009 01:18

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

@laknar,

So what is the question/doubts you are having?

For instance,

Quote:

(start_time+grace_time)

Are you trying to add 15 days to start_time here? Because that's what the above query will do. Or did you mean to add 15 Hrs? or 15 Mins? or 15 secs?? .... Please explain.

Regards,
Jo

[Updated on: Wed, 11 March 2009 01:25]

Report message to a moderator

Re: check if system time falls between start_time and end_time [message #391110 is a reply to message #391104]

Wed, 11 March 2009 01:30

laknar
Messages: 30
Registered: February 2009

Member

grace time is 15 minutes

i have to check if syatem time falls between start_time and (start_time+grace_time) in PL/SQL using IF else statement

then

insert into dept(deptno) values(23);

[Updated on: Wed, 11 March 2009 01:56] by Moderator

Report message to a moderator

Re: check if system time falls between start_time and end_time [message #391113 is a reply to message #391110]

Wed, 11 March 2009 01:42

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

@laknar,

SQL>  SELECT sysdate, sysdate+(15/(24*60)) FROM Dual;

SYSDATE             SYSDATE+(15/(24*60)
------------------- -------------------
11-03-2009 12:25:30 11-03-2009 12:40:30

Convert grace_time to minutes. Hope the above example helps.

Regards,
Jo

Report message to a moderator

Re: check if system time falls between start_time and end_time [message #391120 is a reply to message #391113]

Wed, 11 March 2009 01:56

laknar
Messages: 30
Registered: February 2009

Member

SELECT START_TIME FROM test 
WHERE sysdate <= (start_time+NUMTODSINTERVAL(GRACE_TIME,'MINUTE'))
AND (start_time+NUMTODSINTERVAL(GRACE_TIME,'MINUTE')) >= sysdate

i have tried the above query
is this correct?.

[Updated on: Wed, 11 March 2009 01:57] by Moderator

Report message to a moderator

Re: check if system time falls between start_time and end_time [message #391121 is a reply to message #391120]

Wed, 11 March 2009 01:59

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Put ONLY code between code tags not your whole post.

Quote:

if the system time falls between start_time and (start_time+grace_time)

sysdate between start_time and start_time+NUMTODSINTERVAL(GRACE_TIME,'MINUTE')

As simple as you say it.

Regards
Michel

Report message to a moderator

Re: check if system time falls between start_time and end_time (merged) [message #391205 is a reply to message #391104]

Wed, 11 March 2009 07:50

laknar
Messages: 30
Registered: February 2009

Member

thank you michel
it's working fine.

Report message to a moderator

Previous Topic:	Clob with trim
Next Topic:	Wait Time Command

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 12 04:47:48 CST 2025