Oracle 时区日期格式掩码是什么？

发布于 2024-08-22 06:05:10 字数 676 浏览 5 评论 0原文

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

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”.. ？

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

1 row created.

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

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

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

原文

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

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.

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

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ゞ花落谁相伴 2024-08-29 06:05:10

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

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.

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.

回复收藏 0 原文