hi all,
we have two databases, one is test db which is on a server machine and one is locally installed on developer PC, both have same versions, same NLS_DATE_FORMAT but when trying to extract date + time from sysdate their results are different. definitely something different between both but i failed to check and my required output is to must have valid current time with date to make log in a table. my sqlplus sessions for both are here, please guide me as usual.
SQL> conn scott/tiger@testdb
Connected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
SQL> SELECT value
2 FROM nls_session_parameters
3 WHERE parameter = 'NLS_DATE_FORMAT';
VALUE
--------------------------------------------------------------------------------
DD-MON-RR
SQL>
SQL> select to_char(sysdate, 'dd/mm/rrrr hh24:mi:ss') aa from dual;
AA
-------------------
13/04/2018 00:00:00
SQL>
-- ===============================================
SQL> conn hr/hr@orcl
Connected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
SQL> SELECT value
2 FROM nls_session_parameters
3 WHERE parameter = 'NLS_DATE_FORMAT';
VALUE
--------------------------------------------------------------------------------
DD-MON-RR
SQL>
SQL> select to_char(sysdate, 'dd/mm/rrrr hh24:mi:ss') aa from dual;
AA
-------------------
30/05/2018 13:19:11 -- I want this
i have also tried this
declare
mdate date;
datetime varchar2(50):= 'DD-MON-RRRR HH24:MI:SS';
q1 varchar2(1000);
begin
q1 := 'alter session set nls_date_format = '||chr(39)||datetime||chr(39);
execute immediate q1;
dbms_output.put_line(chr(10)||q1);
select sysdate
into mdate
from dual;
dbms_output.put_line(chr(10)||'mdate = '||to_char(mdate,'dd-mon-rrrr hh24:mi:ss'));
end;
SQL> /
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS'
mdate = 13-apr-2018 00:00:00 -- here result is showing time as 00:00:00
PL/SQL procedure successfully completed.
SQL>
please help.
regards