时区的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 ZONE
orTIMESTAMP 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屋!