Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle auto add current date

Tags:

sql

oracle

I want create a table 'product' and have a column date, is it possible that current date will be added when I add some info to table?

If yes please example of this table

create table products (
id number not null,
date number not null
);
like image 209
Wizard Avatar asked Nov 20 '12 17:11

Wizard


People also ask

How do I get the current date and time in Oracle?

To get the current date and time on the Database Server Oracle has SYSDATE internal value which returns the current date from the operating system on which the database resides. The datatype of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter.

How to add days to date in Oracle?

Oracle allows us to add days to Date using plus (+) operator and Interval data type. Assume that current date is ’06/06/2020′ (DD/MM/YYYY).

How to get current date and time in Oracle SYSDATE internal value?

To get current date and time in Oracle SYSDATE internal value which returns the current date from the operating system on which the database resides. The datatype of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter.

What is an auto-updated column?

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values.


1 Answers

Assuming that

  1. Your column is not actually named date since that is a reserved word
  2. Your column is actually defined as a date rather than as a number
  3. You want to populate the column when you insert a new row

you can define a default value for the column.

SQL> ed
Wrote file afiedt.buf

  1  create table products (
  2  id number not null,
  3  dt date   default sysdate not null
  4* )
SQL> /

Table created.

SQL>
SQL> insert into products( id ) values( 1 );

1 row created.

SQL> select * from products;

        ID DT
---------- ---------
         1 20-NOV-12

If you want to modify the dt column when you UPDATE the row, you would need a trigger

CREATE OR REPLACE TRIGGER trg_products
  BEFORE INSERT OR UPDATE ON products
  FOR EACH ROW
BEGIN
  :new.dt := sysdate;
END;

A trigger will override any value passed in as part of the INSERT or UPDATE statement for the dt column. A default value will not.

like image 134
Justin Cave Avatar answered Sep 19 '22 23:09

Justin Cave