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: 日期格式无法识别

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

SQL> alter system set nls_date_format='yyyy-mm-dd' scope=spfile;

System altered

sql> shutdown immediate
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

SQL> conn scott/system@orcl
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

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博客

oracle如何插入timestamp数据

最新文章