如何在 Oracle 表中插入 DateTime 值

引言

将日期和时间存储在 DATE 字段中是一个很好的习惯。但是，你实际上如何将它们插入到你的表中呢？

在尝试将一个我认为应该有效的值插入到一个表中时，我曾多次迷失方向，但我收到了一些格式或数据类型错误。

所以，我将在本文中通过一个例子来告诉你如何做到这一点。

我们的示例表

让我们创建一个示例表。

CREATE TABLE student (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
date_of_birth DATE
);

此表仅用于我们的示例，所以让我们忽略它没有 ID 字段的事实。

我们如何将数据插入到这个表中？

INSERT INTO student (first_name, last_name, date_of_birth) VALUES ('Adam', 'Jones', '12/01/2016');

这是发生的事情

Error starting at line : 1 in command -
INSERT INTO student (first_name, last_name, date_of_birth) VALUES ('Adam', 'Jones', '12/01/2016')
Error report -
SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:   
*Action:

现在，发生这种情况是因为我提供的日期值 (12/01/2016) 根据该表无效。

我怎样才能让它加载？

使用 TO_DATE 函数。

使用 TO_DATE 函数插入日期时间值

TO_DATE 函数将 string 值转换为 DATE 值。它非常适合将日期插入到表中。

让我们再次尝试，这次使用 TO_DATE 函数。

INSERT INTO student (first_name, last_name, date_of_birth) 
VALUES ('Adam', 'Jones', TO_DATE('12/01/2016', 'DD/MM/YYYY'));

1 row inserted.

这一次，它成功了。

让我们检查一下该表。

SELECT first_name, last_name, date_of_birth
FROM student;

名字	姓氏	出生日期
亚当	琼斯	12/一月/16

如果我更改格式以使用 MM/DD/YYYY，则此 INSERT 语句也可以工作。

INSERT INTO student (first_name, last_name, date_of_birth)
VALUES ('Brad', 'Smith', TO_DATE('12/01/2016', 'MM/DD/YYYY'));

1 row inserted.

让我们再次检查一下该表。

SELECT first_name, last_name, date_of_birth
FROM student;

名字	姓氏	出生日期
亚当	琼斯	12/一月/16
布拉德	史密斯	01/十二月/16

插入日期和时间

这些例子很有用，但它们只显示了日期。时间呢？

让我们尝试另一个例子，但包括一个时间。

INSERT INTO student (first_name, last_name, date_of_birth)
VALUES ('Carrie', 'Johnson', TO_DATE('12/01/2016 14:08:25', 'MM/DD/YYYY HH24:MI:SS'));

1 row inserted.

让我们再次看一下该表。

SELECT first_name, last_name, date_of_birth
FROM student;

名字	姓氏	出生日期
亚当	琼斯	12/一月/16
布拉德	史密斯	01/十二月/16
凯莉	约翰逊	01/十二月/16

等等，时间怎么了？我确定我在 INSERT 语句中包含了时间？

我的确包含了。但是，这里没有显示的原因是 DATE 的默认输出格式可能不包括时间。

我们可以在 NLS_SESSION_PARAMETERS 表中进行检查。

SELECT *
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';

参数	VALUE
NLS_DATE_FORMAT	DD/MON/RR

这意味着每当我们输出 DATE 值时，它将以此格式显示。RR 是一个两位数的年份值。MON 是缩写的月份值。

这与我们的 01/DEC/16 和 12/JAN/16 值匹配。

那么，我们如何显示时间呢？

我们可以通过两种方式来做到这一点。

更改会话日期格式

我们可以更改会话的日期格式，这意味着它只是暂时更改，并在我们结束会话时恢复。

ALTER SESSION SET nls_date_format = 'DD/MON/YYYY hh24:mi:ss';

Session altered.

现在，让我们再次运行我们的查询。

SELECT first_name, last_name, date_of_birth
FROM student;

名字	姓氏	出生日期
亚当	琼斯	12/一月/16
布拉德	史密斯	01/十二月/16
凯莉	约翰逊	01/十二月/16 14:08:25

我们得到相同的行，但时间现在显示出来了。

还有另一种方法可以在不调整会话的情况下显示时间组件。

使用 TO_CHAR 函数格式化输出

你还可以使用 TO_CHAR 函数来格式化输出，它将 date 转换为 varchar 值。

让我们将会话恢复到原来的状态。

ALTER SESSION SET nls_date_format = 'DD/MON/RR';

Session altered.

现在，让我们从我们的表中选择。

SELECT first_name, last_name, date_of_birth
FROM student;

名字	姓氏	出生日期
亚当	琼斯	12/一月/16
布拉德	史密斯	01/十二月/16
凯莉	约翰逊	01/十二月/16

我们可以看到输出现在只显示日期，而不显示时间。

让我们使用 TO_CHAR 函数。

SELECT first_name, last_name, TO_CHAR(date_of_birth, 'DD/MON/YYYY HH24:MI:SS') AS date_output
FROM student;

名字	姓氏	出生日期
亚当	琼斯	12/一月/16
布拉德	史密斯	01/十二月/16
凯莉	约翰逊	01/十二月/16 14:08:25

你可以看到输出类似于我们之前看到的。

我们可以用许多不同的方式输出它。

SELECT first_name, last_name,
TO_CHAR(date_of_birth, 'DD MONTH YY HH:MI:SS AM') AS date_output
FROM student;

名字	姓氏	出生日期
亚当	琼斯	12 一月 16 12:00:00 上午
布拉德	史密斯	01 十二月 16 12:00:00 上午
凯莉	约翰逊	01 十二月 16 02:08:25 下午

所以，你明白了。你可以使用 TO_DATE 函数插入 DATE 和 DATE TIME 值。你可以直接从表中读取值，或者通过更改 session 参数或使用 TO_CHAR 函数来格式化它们以包含时间。