时区的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屋！