Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sysdate problem from a newbie

Re: sysdate problem from a newbie

From: Steve Bell <stephen.bell_at_sympatico.ca>
Date: Fri, 17 Aug 2001 13:41:26 -0400
Message-ID: <3B7D5746.752D385F@sympatico.ca>


Hi,

Try something like this:

SQL> select to_char(sysdate,'YYYYMMDDHHMISS')||'.000'   2 from dual;

TO_CHAR(SYSDATE,'Y



20010817013635.000

SQL> The other problem is you can't subtract ITIME from sysdate because you converted ITIME to a character before you tried to subtract it; You could use to_char(sysdate - 2,'YYYYMMDDHHMISS')||'.000'

TO_CHAR(SYSDATE,'Y



20010815013635.000

SQL> One way or the other though you need to subtract a date value from a date value; you can't convert to_char first then try to subtract..

grAbbY0 wrote:

> Well, the code I have until now is:
>
> select to_char(sysdate, 'sysdate.000')
> ITIME from ALARMS
> where sysdate - ITIME > 2;
>
> But then I get the error at line 3 saying: inconsistent datatypes
>
> "Steve Bell" <stephen.bell_at_sympatico.ca> wrote in message
> news:3B7D3F86.73DF2960_at_sympatico.ca...
> > Hi,
> >
> > Well, your format is only 14 characters and the value is 14 plus ".000"
> >
> > What if you format the 14 date digits using to_char then just concatenate
> the
> > ".000" to the end?
> >
> > grAbbY0 wrote:
> >
> > > Hi there,
> > >
> > > I would appreciate some help here,
> > >
> > > We have a table called Alarms, every day I have to delete all the alarms
> in
> > > this table that were created two days before the current date. (To
> prevent
> > > the table from being filled up). These Alarms are generated in a SCADA
> > > application which generates the time of the Alarms in a column called
> ITIME.
> > > The datatype is char and the value is like: 20010817164555.000
> > > (YYYYMMDDHHMISS). I cant remove the '.000' because this has to be a
> standard
> > > value.
> > > I am trying this with SYSDATE but then I get the error 'date format
> picture
> > > ends before converting entire input string'
> > >
> > > Anybody got an idea??
> > >
> > > Thanx in advance
> >
Received on Fri Aug 17 2001 - 12:41:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US