Oracle Auto Add Current Date

Oracle auto add current date

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.

Automatically populate date in oracle table

Here is how, you need to format your table properly:

create table test (first number
                   , second timestamp default systimestamp
                   , third varchar2(12));

And your default value is always current system time formatted as timestamp.

In Oracle SQL: How do you insert the current date + time into a table?

It only seems to because that is what it is printing out. But actually, you shouldn't write the logic this way. This is equivalent:

insert into errortable (dateupdated, table1id)
    values (sysdate, 1083);

It seems silly to convert the system date to a string just to convert it back to a date.

If you want to see the full date, then you can do:

select TO_CHAR(dateupdated, 'YYYY-MM-DD HH24:MI:SS'), table1id
from errortable;

How can I add the current date in ORACLE SQL column?

In your request comments you have altered your request fundamentally.

One problem: You have an open date range, with a beginning date and an ending date. But when displaying this, you want to substitute the no end" with the current date. This is usually done by storing null (no value) for the date. In a query you can then use COALESCE to replace that with the current date:

SELECT
  empno,
  salary,
  start_date AS first_day,
  COALESCE(end_date, TRUNC(SYSDATE)) AS last_day
FROM salaries;

And for convenience you can make this a view:

CREATE VIEW v_salaries AS SELECT <above query>

Another problem is that you want this null to change when a newer salary information gets added.

Manually you would just

INSERT INTO salaries (empno, salary, start_date, end_date)
VALUES(50, 1200, TRUNC(SYDATE), NULL);

UPDATE salaries SET end_date = TRUNC(SYSDATE) - 1
WHERE empno = 50 AND end_date IS NULL;

If you want this update to happen automatically, there are mainly two options:

1. write a procedure for a new salary that does the insert and update
2. write a trigger to perform the update

Automatically insert a date in database

If you simply do not explicitly set a value for your date column, the default will be used; for example:

insert into assignments( loginid       , 
                         comments      ,
                         status        , 
                         given_by      , 
                         courseanddept 
                        )
                 values ( 'login',
                          'comments',
                          'X',
                          'Y',
                          'unknown'
                        )

The resulting data:

SQL> select adate from assignments;

ADATE
---------
23-JAN-17

If you set a value for the column, the value you set is used and default is ignored:

insert into assignments( loginid       , 
                         comments      ,
                         status        , 
                         given_by      , 
                         courseanddept ,
                         adate 
                        )
                 values ( 'login2',
                          'comments2',
                          'Z',
                          'W',
                          'unknown2',
                          date '2016-12-31'
                        ) 

gives:

SQL> select adate from assignments;

ADATE
---------
31-DEC-16

Same thing if you set a NULL value:

insert into assignments( loginid       , 
                         comments      ,
                         status        , 
                         given_by      , 
                         courseanddept ,
                         adate 
                        )
                 values ( 'login3',
                          'comments3',
                          'Z',
                          'W',
                          'unknown2',
                          null
                        )

gives:

SQL> select adate from assignments;

ADATE
---------

SQL>

Oracle Add Date Column to Existing Table (default to sysdate new rows only)

You need to first add the column without a default:

alter table mytable add date_created date default null;

and then add define the default value:

alter table mytable modify date_created default sysdate;

How to insert a timestamp in Oracle?

insert
into tablename (timestamp_value)
values (TO_TIMESTAMP(:ts_val, 'YYYY-MM-DD HH24:MI:SS'));

if you want the current time stamp to be inserted then:

insert
into tablename (timestamp_value)
values (CURRENT_TIMESTAMP);

Is there an automatic modification time stamp type for Oracle columns?

Tables I've modelled always include:

• CREATED_USER, VARCHAR2
• CREATED_DATE, DATE
• UPDATED_USER, VARCHAR2
• UPDATED_DATE, DATE

...columns. Why implement a trigger when you can set the value at the same time as the INSERT/UPDATE?

INSERT INTO TABLE (...CREATED_DATE, UPDATED_DATE) VALUES (...,SYSDATE, SYSDATE);

UPDATE TABLE
   SET ...,
       UPDATED_DATE = SYSDATE

SQL to check current date with column of type string which stores date in the format MMDD is not 120 days old

As a general disclaimer, your current table design is sub optimal, because a) you are storing dates as text, and b) you are not even storing the year for each date. From what you wrote, it looks like you want to consider all data as having occurred within the last year, from the current date.

One trick we can try here is to compare the MMDD text for each record in your table against TO_CHAR(SYSDATE, 'MMDD'), using the following logic:

• If the MMDD is less than or equal to today, then it gets assigned to current year (2018 as of the time of writing this answer)
• If the MMDD is greater than today, then it gets assigned to previous year (2017).

Then, we may build dates for each record using the appropriate year and check if it is within 120 days of SYSDATE.

WITH yourTable AS (
    SELECT '0101' AS date_col FROM dual UNION ALL
    SELECT '1001' FROM dual UNION ALL
    SELECT '1027' FROM dual UNION ALL
    SELECT '1215' FROM dual
)

SELECT
    date_col
FROM yourTable
WHERE
    (date_col <= TO_CHAR(SYSDATE, 'MMDD') AND
     TO_DATE(date_col || TO_CHAR(SYSDATE, 'YYYY'), 'MMDDYYYY') >= SYSDATE - 120) OR
    (date_col > TO_CHAR(SYSDATE, 'MMDD') AND
     TO_DATE(date_col ||
         TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -12), 'YEAR'), 'YYYY'), 'MMDDYYYY') >=
         SYSDATE - 120);

Demo

---

Related Topics