CODEWITHSUNDEEP
oracle-databaseoracle11goracle12cdate-formatting
user13843534
user13843534

Reputation:

saving data with time in database column

from UI when I am sending date, then in database I can see date without timestamp. When I do

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'

then I can see timestamp as well. But it is session specific. I want this change parmanent.

I want timestamp permanently in database along with date. in logfile it is displaying only date but not time.

what changes I need to do in database? Is there any problem in my understanding

Upvotes: 1

Views: 133

Answers (1)

Littlefoot
Littlefoot

Reputation: 142883

You mean, for the whole database & for all users? Talk to your DBA, they should know it. That would be the nls_date_format initialization parameter.

Alternatively, create the after logon database trigger which will modify that value, e.g.

SQL> show user
USER is "SYS"
SQL> create or replace trigger trg_dflt_date_format
  2    after logon on database
  3  begin
  4    execute immediate q'[alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss']';
  5  end;
  6  /

Trigger created.

What's current format?

SQL> select sysdate from dual;

SYSDATE
--------
13.07.20

OK, logout and login again:

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

C:\temp>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Pon Srp 13 16:55:08 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select sysdate from dual;

SYSDATE
-------------------
13.07.2020 16:55:12

SQL>

Looks OK.

However: topic title says:

saving data with time in database column

Unless you "remove" the time component (e.g. by truncating the value, such as trunc(sysdate)), Oracle will store both date and time. It is just the way you display it.

One option is to use alter session (as you already know), another is to use to_char function with desired format mask, e.g.

SQL> select to_char(sysdate, 'hh24:mi:ss yyyy-mm-dd') right_now from dual;

RIGHT_NOW
-------------------
16:59:29 2020-07-13

SQL>

Upvotes: 1

Related Questions