CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
ScottW
ScottW

Reputation: 69

NULL Date field is populated with SYSDATE by default

I am running a query on clocking data for my company. I have a query with 2 fields: CLOCK_IN1 and CLOCK_OUT1. When I run the query I see the proper CLOCK_IN1 but the CLOCK_OUT1 is being populated with a date near SYSDATE. CLOCK_OUT1 is NULL in the database and it's replacing the NULL with this date.

enter image description here

Is there a setting that populates a date when it's NULL?

Upvotes: 0

Views: 847

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

There's either

  • a DEFAULT value applied to that column, or
  • a database trigger that does it.

This is how the first works:

SQL> create table test
  2    (clock_in1    date,
  3     clock_out1   date   default sysdate
  4    );

Table created.

SQL> insert into test (clock_in1) values (date '2020-08-23');

1 row created.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
03.11.2020 20:23:03

SQL> select * from test;

CLOCK_IN1           CLOCK_OUT1
------------------- -------------------
23.08.2020 00:00:00 03.11.2020 20:22:48

SQL>

And this is the second:

SQL> drop table test;

Table dropped.

SQL> create table test
  2    (clock_in1    date,
  3     clock_out1   date
  4    );

Table created.

SQL> create or replace trigger trg_bi_test
  2    before insert on test
  3    for each row
  4  begin
  5    :new.clock_out1 := nvl(:new.clock_out1, sysdate);
  6  end;
  7  /

Trigger created.

SQL> insert into test (clock_in1) values (date '2020-08-23');

1 row created.

SQL> select * from test;

CLOCK_IN1           CLOCK_OUT1
------------------- -------------------
23.08.2020 00:00:00 03.11.2020 20:24:23

SQL>

How to "fix" it?

SQL> alter table test modify clock_out1 default null;

Table altered.

SQL>

As of the trigger, well ... it depends on what it is doing, but - removing a line that sets column value would do.

Upvotes: 1

Related Questions