SQL> select * from t_timestamp;
A
--------------------------------------------------------------------------------
14-11月-12 10.46.24.195000 上午
SQL> show parameter time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
cursor_space_for_time boolean FALSE
ddl_lock_timeout integer 0
distributed_lock_timeout integer 60
log_checkpoint_timeout integer 1800
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
parallel_min_time_threshold string AUTO
resumable_timeout integer 0
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL> alter session set nls_time_format='yyyy-mm-dd hh24:mi:ss.ff';
alter session set nls_time_format='yyyy-mm-dd hh24:mi:ss.ff'
ORA-01821: 日期格式无法识别
A
--------------------------------------------------------------------------------
14-11月-12 10.46.24.195000 上午
SQL> show parameter time
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
cursor_space_for_time boolean FALSE
ddl_lock_timeout integer 0
distributed_lock_timeout integer 60
log_checkpoint_timeout integer 1800
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
parallel_min_time_threshold string AUTO
resumable_timeout integer 0
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL> alter session set nls_time_format='yyyy-mm-dd hh24:mi:ss.ff';
alter session set nls_time_format='yyyy-mm-dd hh24:mi:ss.ff'
ORA-01821: 日期格式无法识别
SQL> conn
sys/system@orcl as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> show parameter nls
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> show parameter nls
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
SQL> alter system set nls_date_format='yyyy-mm-dd' scope=spfile;
System altered
sql> shutdown immediate
sql>startup
sql>startup
SQL> show parameter nls
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string yyyy-mm-dd
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string BINARY
nls_currency string
nls_date_format string yyyy-mm-dd
SQL> conn
scott/system@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
Session altered
Session altered
SQL> select * from t_timestamp;
A
--------------------------------------------------------------------------------
2012-11-14 10:46:24.195000
SQL> insert into t_timestamp values('2012-12-01 09:21:13.1');
1 row inserted
SQL> select * from t_timestamp;
A
--------------------------------------------------------------------------------
2012-11-14 10:46:24.195000
2012-12-01 09:21:13.100000
小结;1,nls_timestamp_format与nls_date_format关联,必须先配置后者
不然会报ORA-01821: 日期格式无法识别
2,timestamp数据可以用to_timestamp自定义格式插入数据,道理
和to_date一样
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-749182/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-749182/