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: grAbbY0 <grAbbY0_at_hotmail.com>
Date: Sat, 18 Aug 2001 12:20:00 GMT
Message-ID: <Q3tf7.465200$u5.9855365@zwoll1.home.nl>


thx a lot !!!!
happy everybody helps here

"Steve Bell" <stephen.bell_at_sympatico.ca> wrote in message news:3B7D5746.752D385F_at_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 Sat Aug 18 2001 - 07:20:00 CDT

Original text of this message

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