OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » How do I...?

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

How do I...? [message #188669]

Mon, 21 August 2006 03:08

cacheboy
Messages: 21
Registered: August 2006

Junior Member

Having a varchar2 variable with value 20060125123030 (in the format of YYYYMMDDHHMMSS), how do I know if this value is +- X minutes from the current system time?

Don't know how to write this code. Can anyone point me in a direction(s)?

Report message to a moderator

Re: How do I...? [message #188671 is a reply to message #188669]

Mon, 21 August 2006 03:12

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

You need to convert the format mask th YYYYMMDDHHMISS
You may alsoe want to convert the HH to HH24.

You should also store dates in DATE types, not strings.

  1  SELECT abs(sysdate - to_Date('20060125123030', 'YYYYMMDDHHMISS'))*(24*60) minutes_from_sysdate
  2* FROM dual
SQL> /

MINUTES_FROM_SYSDATE
--------------------
            299320.9

Report message to a moderator

Re: How do I...? [message #188674 is a reply to message #188669]

Mon, 21 August 2006 03:17

Frank Naude
Messages: 4581
Registered: April 1998

Senior Member

Another solution:

SQL> SELECT DECODE( GREATEST( TO_DATE('20060125123030', 'YYYYMMDDHHMISS'),
  2                           SYSDATE),
  3                 SYSDATE,
  4                 '-',
  5                 '+')
  6    FROM dual;

D
-
-

Report message to a moderator

Previous Topic:	Conversion of VARCHAR2 to XMLTYPE possible?
Next Topic:	n number of CREATE statements in single procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 10 03:30:02 CST 2024