DB2 将数字转换为日期

Question

由于某些原因（我无法控制），我需要查询的iSeries AS400 DB2系统中，日期被存储为整数。例如，今天将被存储为：

20,171,221

我在英国，需要按照以下日期格式：

21/12/2017

这是我的查询结果：（OAORDT = 日期字段）

Select
Date(SUBSTR( CHAR( OAORDT ),7,2) ||'/' || SUBSTR(CHAR ( OAORDT ),5,2) || '/' || SUBSTR(CHAR (OAORDT ),1,4)) AS "Order Date"
from some.table

然而，我得到的都是空值。如果我移除日期函数，它就可以正常工作，但此时它变成了一个字符串，这不是我想要的：

Select
SUBSTR( CHAR( OAORDT ),7,2) ||'/' || SUBSTR(CHAR ( OAORDT ),5,2) || '/' || SUBSTR(CHAR (OAORDT ),1,4) AS "Order Date"
from some.table

如何将OAORDT字段转换为日期格式？

更新一下 - 我将使用OpenQuery从MS SQL Server查询此信息。

谢谢。

- Michael

---

哪个版本的 i 系列？ - mustaccio

不要在 *EUR 中这样做，而是在 *ISO 中这样做：'YYYY-MM-DD'。 - Clockwork-Muse

@mustaccio 这是7 r2版本。 - Michael

@Clockwork-Muse，恐怕我不理解你的评论。 - Michael

@Michael 等等，似乎有些地方出了问题。你真正需要什么？下面的“我必须接受它”的评论似乎表明有些混淆。Date() 返回一个本地的 Date 值。这是一个具有内部格式的东西，您永远看不到，并且据我所知，IBM 从未公布过它。日期字段还具有外部格式，该格式由 *DATFMT 选项控制。所有这些都是为了说，如果您想要一个 Date 值，则唯一重要的格式是要转换为日期的字符串的格式。请参见我的答案。 - jmarkmurphy

日期就是日期，直到显示给用户之前它并没有一个具体的格式。显示给用户的格式可以在你使用的工具中进行配置。确保你的工具已经设置为dd/mm/yyyy。 - Charles

Answer 1

1）如何将OAORDT字段转换为日期？
最简单的方法是使用 TIMESTAMP_FORMAT ：

SELECT DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD'))

2) 作为在英国的用户，我需要日期格式为 21/12/2017 :

SELECT VARCHAR_FORMAT(DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD')),'DD/MM/YYYY')

- Stavr00

---

这个是有效的，但如果我改成：DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'DDMMYYYY')) AS TEST2，那么我又会得到空值？ - Michael

@Michael 可能是因为你的日期字符串格式是 YYYYMMDD 而不是 DDMMYYYY。 - mustaccio

啊，好的。我必须接受它，因为我无法在IBM上进行任何更改。 - Michael

为什么需要使用日期字段？请看我的回答。 - jmarkmurphy

最后一行不太好，现在他得到的是一个字符串，而不是一个日期。 - Charles

数据库引擎层不应该决定如何表示日期，这是由调用应用程序来完成的。但是OP发布了一个由两部分组成的问题。 - Stavr00

Answer 2

请注意，您没有说明在哪里进行此操作，但由于您标记为ibm-midrange，我将回答嵌入式SQL的相关问题。如果您想要使用JDBC、ODBC或交互式SQL，概念是相似的，只是实现方式不同。

确保SQL使用正确的日期格式，默认值为*ISO。对于您来说，应该是*EUR。在RPG中，您可以这样做：

exec sql set option *datfmt = *EUR;

请确保set option是程序中的第一条SQL语句，我通常会将其直接放在D和C规范之间。

请注意，这不是程序的最佳解决方案。最佳做法是将RPG和SQL日期格式都设置为*ISO。我喜欢明确地设置它们。RPG日期格式由...设置。

ctl-opt DatFmt(*ISO);

SQL日期格式是由以下设置的

exec sql set option *datfmt = *ISO;

现在所有内部日期都以ISO格式处理，并且没有年份范围限制（年份可以是0001-9999）。您可以以任何格式显示或打印。同样，您可以以任何格式接收输入。

编辑日期是一种独特的存在。并非每种语言或操作系统都知道如何处理它们。如果您正在寻找日期值，则唯一需要指定的格式是要转换为日期的字符串的格式。您不需要（也无法）指定日期字段的内部格式，日期字段的外部格式可以是您想要的任何东西，并且每次使用时都可以不同。因此，当您像@Stavr00提到的那样使用时：

DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD'))

提供的格式不是日期字段的格式，而是要转换为Timestamp的数据的格式。然后Date()函数将Timestamp值转换为Date值。此时格式无关紧要，因为无论您指定了哪个外部格式*DATFMT，时间戳都在内部时间戳格式中，日期值在内部日期格式中。下一次格式重要的时候是当您将日期值呈现给用户作为字符串或数字时。此时，格式可以设置为*ISO、*EUR、*USA、*JIS、*YMD、*MDY、*DMY或*JUL，在某些情况下还可用*LONGJUL和*Cxxx格式。

- jmarkmurphy

---

是的，这可能是为什么它返回null的原因，但我不喜欢这样做的方向。 - Clockwork-Muse

基本上，你的答案是“设置一个隐藏依赖项” - 这可能会导致事情出错。最好删除/显式提供所有依赖项；比如，首先将日期转换为*ISO格式，而不是*EUR。 - Clockwork-Muse

是的，我知道。问题在于你无法知道还有什么其他内容会共享该会话并期望除了 *EUR 之外的其他内容。会话状态是隐藏的全局状态，可以被进程中的任何东西改变。 - Clockwork-Muse

那是错误的。设置选项仅影响编译进该程序中的部分，而不是全局设置。 - jmarkmurphy

新程序将使用自己的选项集，或者在编译命令上指定的属性（默认为“JOB”）。这就是为什么我更喜欢明确设置RPG和SQL的DATFMT。 - jmarkmurphy

Answer 3

由于没有任何一种变量适合我的需求，我自己想出了一个。

它非常简单：

select * from yourschema.yourtable where yourdate = int(CURRENT DATE - 1 days) - 19000000;

这个days考虑了闰年的因素，并且适合大多数需求。

同样的方式，days可以转换为months或years。 不需要像VARCHAR_FORMAT/TIMESTAMP_FORMAT这样的重型武器。

- im_infamous

Answer 4

以下方法适用于我：

select date(substring(trim(DateCharCol), 1, 2)||'/'||substring(trim(DateCharCol), 3, 2)||'/'||'20'||substring(trim(DateCharCol), 5, 2)) from yourTable where TableCol =?;

- Noor

Answer 5

当我使用时，也会得到 null 值。

SELECT DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD'))

将 CHAR 改为 DIGITS 给我带来了所需的结果。

SELECT DATE(TIMESTAMP_FORMAT(DIGITS(OAORDT),'YYYYMMDD'))

- Kpgmr72