Toad World® Forums

Display of time with date in the table


#1

CREATE OR REPLACE TRIGGER TR_BASICS_CREATED

ON TBL_BASICS REFERENCING NEW AS New OLD AS OldFOR

BEGIN

IF :NEW.created_on IS NULL THEN

SELECT SYSDATE

INTO :NEW.created_on FROM DUAL;

end if;

END TR_BASICS_CREATED;

Thanks.

I use Toad to query records from the oracle table. When a new record is inserted the database uses the following trigger to insert the created on date. On retrieving the records from the table, I see the time portion is displayed only for certain records eventhough the same trigger is used for all the inserts. Does it depend on any TOAD settings? If so, what is it?

BEFORE INSERT EACH ROW


#2

It sounds like “created_on” is a varchar2 field. The records should appear one way (date with no time) or the other (date with time) - not intermingled.

If “created_on” is in fact a date, you may want to check your NLS parameters. These can affect how records are displayed to you by default.

  SELECT *
FROM V$NLS_PARAMETERS
where parameter like '%FORMAT';

An alternative you can try, is formatting the date with to_char.

  select to_char(created_on, 'YYYY-MM-DD HH24:MI:SS') as created_on
from tbl_basics;