SQL 如何在 DB2 AS/400 中将十进制字段转换为日期字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3038769/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I cast a decimal field to a date field in DB2 AS/400?
提问by kevin
I have a DECIMAL field which contains a date in the AS400 format: 1100614
我有一个 DECIMAL 字段，其中包含 AS400 格式的日期：1100614
I have tried:
我试过了：
cast (MYDATE as DATE)
but I can't cast a DECIMAL to DATE, whereas
但我不能将 DECIMAL 转换为 DATE，而
DATE(MYDATE)
returns null.
返回空值。
How do I cast this field in a DATE field?
如何在 DATE 字段中转换此字段？
回答by Mike Wills
Assuming it is CYYMMDD like I asked in my comment above, here are a couple options:
假设它是 CYYMMDD，就像我在上面的评论中所问的那样，这里有几个选项：
It can be done, but it isn't easy. If you need to do this often, it may be worth your time writing your own function to do this.
这是可以做到的，但这并不容易。如果您需要经常执行此操作，则可能值得您花时间编写自己的函数来执行此操作。
回答by user293106
At a recent DB2 for IBM i class I attended, IBM folks were suggesting a pre-populated conversion table, that contains the date values in all the different formats you need to support. You only need 365 or 366 rows per year. You then join to this table to convert. Of course you'll need to create the indexes for each format to have good performance. One guy stated the performing the join could even be faster than calling a conversion function.
在我最近参加的 DB2 for IBM i 课程中，IBM 的人建议使用一个预先填充的转换表，其中包含您需要支持的所有不同格式的日期值。您每年只需要 365 或 366 行。然后加入该表进行转换。当然，您需要为每种格式创建索引以获得良好的性能。一个人说执行连接甚至可能比调用转换函数更快。
Another advantage is that you can add other columns in the conversion table such as the fiscal period, the quarter, business day indicator, etc, which can simplify a bunch of queries, such as finding all orders in the third quarter.
另一个好处是可以在转换表中添加其他列，例如会计期间、季度、营业日指标等，可以简化一堆查询，例如查找第三季度的所有订单。
For instance, say the DATECONV table has columns DATEFMT as a real date and CYMDFMT as the decimal date, your select would be SELECT DATEFMT, ... FROM MYTABLE JOIN DATECONV on CYMDFMT=MYDATE
例如，假设 DATECONV 表的列 DATEFMT 作为实际日期，CYMDFMT 作为十进制日期，您的选择将是 SELECT DATEFMT, ... FROM MYTABLE JOIN DATECONV on CYMDFMT=MYDATE
One issue with this solution is that if you use invalid dates as special values such as 9999999 as you can't have invalid dates in the real date field. But then you can't convert them anyway.
此解决方案的一个问题是，如果您使用无效日期作为特殊值（例如 9999999），因为在实际日期字段中不能有无效日期。但是无论如何你都不能转换它们。
回答by twblamer
This will work if you're on IBM i release 6.1 or newer, but I have a hunch you're on an old version of the operating system. I stole the adding trick from this forum post.
如果您使用的是 IBM i 6.1 或更高版本，这将起作用，但我预感您使用的是旧版本的操作系统。我从这个论坛帖子中窃取了添加技巧。
create table dec1 (f1 decimal(7,0))
insert into dec1 values 1100614
select date(timestamp_format(char(f1+19000000), 'YYYYMMDD')) from dec1
Results:
结果：
DATE
06/14/10
******** End of data ********
Here's the referenceto the supported date string formats in DB2 for i SQL.
回答by Jeganinfo
A simpler (and more suitable if you want your own function) way to do this would be
一个更简单（如果你想要你自己的函数更合适）的方法是
SELECT CAST(CAST(*DecimalDateFromDB2_Here* + 19000000 AS VARCHAR) AS DATETIME)
Example:
例子：
-Showing 2000 + dates
- 显示 2000 多个日期
SELECT CAST(CAST(1040526 + 19000000 AS VARCHAR) AS DATETIME)
-Showing 1999 + dates
- 显示 1999 + 日期
SELECT CAST(CAST(990526 + 19000000 AS VARCHAR) AS DATETIME)
回答by CRPence
An [and rather simple] expression verified to function as far back as v5r3, and for which the MYDATE column may be defined as any of CHAR(7), DEC(7), NUMERIC(7), INTEGER or FLOAT data types [or a larger length/precision variant of those types] follows; the DATE casting scalar shown in this example, is converting the result of a character-string expression [per CONCAT] that yields a 14-character string that will be valid as a TIMESTAMP with the format 'YYYYMMDDHHMMSS':
一个 [而且相当简单的] 表达式已验证可以追溯到 v5r3，并且可以将 MYDATE 列定义为任何 CHAR(7)、DEC(7)、NUMERIC(7)、INTEGER 或 FLOAT 数据类型 [或这些类型的更大长度/精度变体]如下；此示例中显示的 DATE 转换标量正在转换字符串表达式 [per CONCAT] 的结果，该表达式生成一个 14 个字符的字符串，该字符串将作为有效的 TIMESTAMP 格式'YYYYMMDDHHMMSS'：
DATE( DEC( 19000000 + MYDATE, 8, 0 ) CONCAT '000000' )
Note: The above expression may be [seen elsewhere] written with a DIGITS casting scalar for the DECIMAL casting result, but that would be superfluous given the addition of a value with a full 8-digits of precision, and implicit castfrom numeric to character for the DEC() expression; the DIGITS is required when dealing with dates prior to year 1000 to ensure a leading zero in the string result, or on some even older releases where there is no capability of implicit castfrom numeric to character.
注意：上面的表达式可能 [在别处看到] 用 DIGITS 转换标量来编写 DECIMAL 转换结果，但考虑到添加一个具有完整 8 位精度的值，以及从数字到字符的隐式转换，这将是多余的对于 DEC() 表达式；在处理 1000 年之前的日期时需要 DIGITS 以确保字符串结果中的前导零，或者在一些没有从数字到字符的隐式转换功能的更旧版本上。
IBM i 7.2->数据库->参考->SQL 参考->语言元素->数据类型->日期时间值->日期时间值的字符串表示->时间戳字符串
