Date function in Oracle

Date function in Oracle

 examples of date function:

   insert into mydate values (sysdate -1);

   insert into mydate values (sysdate - 6/24); #   6 hours ago

  insert into mydate values (sysdate - 720/1440); # 12 hours ago
  click read for complete example

usage

 delete noprompt archivelog until time 'sysdate - 1';

 delete archivelog all backed up 1 times to device type disk completed before 'sysdate-6/24';

 

system@MZMD> create table mydate ( dt date );

Table created.

system@MZMD> insert into mydate values (sysdate);

1 row created.

system@MZMD> select * from mydate;

DT

--------------------

DEC-13-2010 18:40:16

system@MZMD> insert into mydate values (sysdate -1);

1 row created.

system@MZMD>  select * from mydate;

DT

--------------------

DEC-13-2010 18:40:16

DEC-12-2010 18:40:36

system@MZMD>  insert into mydate values (sysdate);

1 row created.

system@MZMD> insert into mydate values (sysdate - 1/1440);

1 row created.

system@MZMD>  select * from mydate;

DT

--------------------

DEC-13-2010 18:41:32

DEC-13-2010 18:40:36

system@MZMD> system@MZMD>  insert into mydate values (sysdate - 6/24);

1 row created.

system@MZMD> insert into mydate values (sysdate);

1 row created.

system@MZMD>  select * from mydate;

DT

--------------------

DEC-13-2010 12:42:09

DEC-13-2010 18:42:15

system@MZMD>  insert into mydate values (sysdate - 720/1440);

#  (where 720=60*12, which 12 hours ago)

1 row created.

system@MZMD> insert into mydate values (sysdate);

1 row created.

system@MZMD>  select * from mydate;

DT

--------------------

DEC-13-2010 06:45:32

DEC-13-2010 18:45:35

15 rows selected.