时间格式设置
<pre><code>TIMESTAMP
drop table pdm_date;
create table pdm_date(barcode varchar2(30),start_date TIMESTAMP(3),stop_date TIMESTAMP(3));
insert into pdm_date values('12345',to_timestamp('2021/08/26 10:55:08.251','yyyy/mm/dd hh24:mi:ss.ff3'),sysdate);
commit;
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_TIMESTAMP_FORMAT';
select * from pdm_date;
select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_DATE_FORMAT';
select sysdate from dual;
alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS:FF6';
show parameter nls
alter system set NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS:FF6' scope=spfile;
select sysdate from dual;
echo $NLS_DATE_FORMAT
export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'
echo $NLS_TIMESTAMP_FORMAT
export NLS_TIMESTAMP_FORMAT='yyyy/mm/dd hh24:mi:ss.ff'</code></pre>
<h2>时间格式设置</h2>
<p><a href="https://blog.csdn.net/qq_35599860/article/details/79414497">参考文章</a></p>
<p>优先级如下(低到高)：初始化参数 < 系统环境变量 < 会话级(session)< 函数</p>
<pre><code>--初始化参数修改以及验证修改是否正常；
SQL> alter system set NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS' scope=spfile;
SQL> create pfile='testorcl.ora' from spfile;
[oracle@oracle dbs]$ cd /u01/app/oracle/product/12.2.0/db_1/dbs/
[oracle@oracle dbs]$ cat testorcl.ora
*.nls_date_format='YY/MM/DD HH24:MI:SS'
--系统变量查询、设置、取消设置
[oracle@oracle dbs]$ echo $NLS_DATE_FORMAT
[oracle@oracle dbs]$ export NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'
[oracle@oracle dbs]$ unset NLS_DATE_FORMAT
--设置会话时间格式与查询
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SQL> show parameter nls_date_format
SQL> select to_char(sysdate,'mm-dd-yy hh24:mi:ss') from dual;</code></pre>