Re: oracle question to get Date minus 1

pawan_test wrote:
> can anyone also please suggest me for the following
>
> this is oracle question
>
> say TIME=20060928
> desired output: 20060927
>
> say TIME=20061001
> DESIRED output: 20060930
>
> If i substract one day from 'TIME' i am looking for previous day.
>
> can anyone please suggest me how do i do this is SQL.
>
> thanks
> mark

SQL> create table timetest (time date);

Table created.

SQL> insert into timetest(time) values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select time, time - 1
  2 from timetest;

TIME TIME-1
-------- --------
20060928 20060927

SQL> Of course if TIME isn't a DATE field you'll need to convert it:

SQL> drop table timetest;

Table dropped.

SQL> create table timetest(time varchar2(8));

Table created.

SQL> insert into timetest(time) values ('20061001');

1 row created.

SQL> commit;

Commit complete.

SQL> select time, to_date(time, 'YYYYMMDD') - 1   2 from timetest;

TIME TO_DATE(
-------- --------
20061001 20060930

SQL> David Fitzjarrell Received on Thu Sep 28 2006 - 20:00:54 CDT