[20240529]如何将字符型数据插入表时自动转成日期型.txt

--//链接上的提问,自己尝试看看.
--//https://imgtraffic.com/i-1/2024/05/29/6656ab43c9520.jpeg.html
如何将字符型数据插入表时自动转成日期型
如下，b字段插入时为字符型，想要在不改变插入语句情况下将其保存成日期格式
create table t(a int,b date);
insert into t values(1,'2016-09-01');   

--//测试看看:

d:\tmp> set | grep -i nls
set | grep -i nls
NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF
NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF

SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t(a int,b date);
Table created.

SCOTT@test01p> insert into t values(1,'2016-09-01');
1 row created.

SCOTT@test01p> commit;
Commit complete.

SCOTT@test01p> select * from t;
         A B
---------- -------------------
         1 2016-09-01 00:00:00

--//只要设置NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS就可以.在会话级别修改看看:

SCOTT@test01p> alter session set NLS_DATE_FORMAT='YYYY-DD-MM HH24:MI:SS';
Session altered.
--//注意日期是年日月.

SCOTT@test01p> insert into t values(2,'2016-09-01');
1 row created.

SCOTT@test01p> commit;
Commit complete.

SCOTT@test01p> select * from t;
         A B
---------- -------------------
         1 2016-01-09 00:00:00
         2 2016-09-01 00:00:00

--//注意日期格式是年日月.这样第1个日期显示变成了2016-01-09 00:00:00.