时间戳

首页 > 解决方案 > 在oracle sql中将字符串格式的日期转换为日期时间格式

sql - 在oracle sql中将字符串格式的日期转换为日期时间格式

问题描述

我的日期列值采用字符串格式，并显示诸如“44370.4087037037”、“44370.4087384259”之类的值。而我希望它们采用 MM/DD/YYYY HH:MM 格式。我必须将它输入到 Oracle 中的数据库中。我的查询是 -

INSERT ALL 
INTO TABLE1 (NAME, DATE_COL) VALUES ('ABC' , '44370.4087037037')
INTO TABLE1 (NAME, DATE_COL) VALUES ('XYZ' , '44370.4087384259')
SELECT * FROM DUAL;

我应该如何转换它？我尝试了 TO_DATE、转换、强制转换 - 不起作用它给出了错误 - [错误] 执行 (5: 23): ORA-00907: 缺少右括号

标签： sqldatabaseoracle

解决方案

假设您的值是 1899-12-31 之后的天数，那么您可以使用：

INSERT ALL 
INTO TABLE1 (NAME, DATE_COL) VALUES ('ABC' , DATE '1899-12-31' + 44370.4087037037 )
INTO TABLE1 (NAME, DATE_COL) VALUES ('XYZ' , DATE '1899-12-31' + 44370.4087384259 )
SELECT * FROM DUAL;

然后，对于您的表：

CREATE TABLE table1(
  name VARCHAR2(10),
  date_col DATE
);

输出将是：

SELECT * FROM table1;

输出（当NLS_DATE_FORMAT是YYYY-MM-DD HH24:MI:SS）：

姓名 DATE_COL
美国广播公司 2021-06-24 09:48:32
XYZ 2021-06-24 09:48:35

而我希望它们采用 MM/DD/YYYY HH:MM 格式。

一DATE列存储为 7 字节的二进制数据（每个世纪 1 个字节。世纪、月、日、小时、分钟和秒）并且没有格式。

如果您想要一个格式化的日期，那么您需要TO_CHAR在SELECT语句中使用将其更改为格式化的字符串：

SELECT name, TO_CHAR( date_col, 'MM/DD/YYYY HH24:MI' ) AS date_str
FROM   table1;

哪个输出：

姓名 DATE_STR
美国广播公司 2021 年 6 月 24 日 09:48
XYZ 2021 年 6 月 24 日 09:48

db<>在这里摆弄

如果您使用 Excel 日期值

从我在这里的回答来看，Excel 中存在一个错误，它认为 1900 年是闰年，并且它分配的天数超出了 1。如果您想使用 Excel 值，那么假设您的所有值都在之后，1900-02-28那么您想1899-12-30用作 epoch 值而不是1899-12-31.

如果您要混合使用之前和之后的值，那么您可以根据先前链接的答案1900-02-28使用表达式。CASE

推荐阅读