什么是时区的 Oracle 日期格式掩码? [英] What is the Oracle date formatting mask for time zones?

问题描述

我需要从外部来源插入包含时区三字母代码的日期格式，但 TZD 格式掩码似乎不起作用...

I need to insert a date format from an outside source which includes the three letter code for time zone, but the TZD formatting mask does not seem to work...

insert into blah
    values (to_date('Thu, 18 Feb 2010 08:37:00 EST','Dy, DD Mon YYYY HH24:MI:SS TZD'));

ORA-01821: date format not recognized

如果我删除TZD"...

If I remove the "TZD"...

insert into blah
    values (to_date('Thu, 18 Feb 2010 08:37:00','Dy, DD Mon YYYY HH24:MI:SS'));

1 row created.

Oracle 中此类插入语句的正确掩码是什么?

What is the proper mask for such an insert statement in Oracle?

desc blah
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 D                          DATE

我将表列从 DATE 类型更改为 TIMESTAMP 类型并得到相同的错误.

I changed the table column from DATE type to TIMESTAMP type and got the same error.

推荐答案

日期列没有时区选项.您必须将列创建为数据类型 TIMESTAMP WITH TIME ZONE 或 TIMESTAMP WITH LOCAL TIME ZONE，此外，TO_DATE 函数不会'不了解您应用的 TIME ZONE 格式掩码.

Date columns don't have timezone as an option. You'd have to create the column as data type TIMESTAMP WITH TIME ZONEorTIMESTAMP WITH LOCAL TIME ZONE, and besides, the TO_DATE function doesn't understand the TIME ZONE format mask you're applying.

SQL> CREATE TABLE T
  2  (DT DATE,
  3   TS TIMESTAMP,
  4   TSTZ TIMESTAMP WITH TIME ZONE,
  5   TSLTZ TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> INSERT INTO T (TSLTZ) VALUES 
  2  (to_timestamp_tz('Thu, 18 Feb 2010 08:37:00 EST','DY, DD Mon YYYY HH24:MI:SS TZD'))
  3  /

1 row created.

SQL> INSERT INTO T (TSTZ) VALUES 
  2  (to_timestamp_tz('Thu, 18 Feb 2010 08:37:00 EST','DY, DD Mon YYYY HH24:MI:SS TZD'))
  3  /

1 row created.

这篇关于什么是时区的 Oracle 日期格式掩码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！