英文:
Teradata Error 5407 when casting INTEGER to TIME
问题
以下是翻译好的部分：
这似乎应该很简单，我有一个表中的两个字段。一个是日期字段，另一个是整数。我试图将它们合并为单个日期时间。
INTEGER字段存储的是时间，其中0应该是00:00:00，235959是23:59:59。因为datefield已经是DATE类型，将其转换为TIMESTAMP(0)是可行的。问题出在我尝试将INTEGER字段转换为TIME(0)时。我得到了一个错误5407 '无效的DateTime操作'。
所以我尝试将其先转换为字符，然后再转换为时间。同样的错误。我尝试使用格式'hh:mi:ss'将其转换为时间。同样的错误。
作为最后的尝试，我尝试了：
SELECT CAST(235959 AS TIME(0));
同样的错误。所以我被难住了，搜索只指向了我已经尝试过的东西。我漏掉了什么？
英文:
This seems like it should be simple, I have two fields in a table. One is a DATE field and the other is an INTEGER. I am trying to combine these into a single DATETIME.
SELECT datefield,
timefield,
CAST(datefield AS TIMESTAMP(0)) + CAST(timefield as TIME(0)) AS datetimefield
FROM table;
The INTEGER field is storing the time where 0 should be 00:00:00 and 235959 is 23:59:59. Because the datefield is a DATE type already, casting it to TIMESTAMP(0) works. The issue is when I try to cast the INTEGER field to TIME(0). I get an error 5407 'Invalid operation for DateTime'.
So I tried to cast to character then to time. Same error. I tried to cast to time with a format of 'hh:mi:ss'. Same error.
As a last attempt I tried:
SELECT CAST(235959 AS TIME(0));
Same error. So I am stumped and searches only point to things I have already tried. What am I missing?
答案1
得分: 0
第一个问题是，您实际上不想将其转换为 TIME。为了将偏移添加到 TIMESTAMP，您的 235959 需要转换为 INTERVAL HOUR TO SECOND。第二个问题是，没有内置的转换方法可以将 "编码整数值" 转换为具有不同单位的值。但是有多种间接方法可以将整数转换为所需的间隔：
/* 提取每个单位并用作乘法器 */
((timefield/10000)*INTERVAL '1' HOUR)+(((timefield/100) MOD 100)*INTERVAL '1' MINUTE)+((timefield MOD 100)*INTERVAL '1' SECOND)
/* 本质上相同，但使用单位转换函数 */
NumToDSInterval(timefield/10000,'HOUR')+NumToDSInterval((timefield/100) MOD 100,'MINUTE')+NumToDSInterval((timefield MOD 100),'SECOND')
/* 计算最细粒度单位的等效整数，然后转换 */
NumToDSInterval((timefield/10000)*3600+((timefield/100) MOD 100)*60+(timefield MOD 100),'SECOND')
/* 转换为字符字符串然后再转回 */
CAST(CAST(CAST(timefield AS FORMAT '99:99:99') AS CHAR(8)) AS INTERVAL HOUR TO SECOND)
或者忘记 INTERVAL 类型，只需构建一个可以转换为 TIMESTAMP 的字符串，例如：
CAST(TO_CHAR(datefield)||CAST(CAST(timefield AS FORMAT '999999') AS CHAR(6)) AS TIMESTAMP(0) FORMAT 'yyyy/mm/ddhhmiss')
英文:
The first problem is that you don't really want to convert to TIME. In order to add an offset to TIMESTAMP your 235959 needs to be converted to INTERVAL HOUR TO SECOND. The second problem is that there is no built-in conversion for an "encoded integer value" with different units. But there are multiple indirect ways to convert the INTEGER to the desired INTERVAL:
/* Extract each unit and use as a multiplier */
((timefield/10000)*INTERVAL '1' HOUR)+(((timefield/100) MOD 100)*INTERVAL '1' MINUTE)+((timefield MOD 100)*INTERVAL '1' SECOND)
/* Essentially the same but use unit conversion function */
NumToDSInterval(timefield/10000,'HOUR')+NumToDSInterval((timefield/100) MOD 100,'MINUTE')+NumToDSInterval((timefield MOD 100),'SECOND')
/* Calculate an equivalent integer in the most granular unit, then convert */
NumToDSInterval((timefield/10000)*3600+((timefield/100) MOD 100)*60+(timefield MOD 100),'SECOND')
/* Cast to a character string and back */
CAST(CAST(CAST(timefield AS FORMAT '99:99:99') AS CHAR(8)) AS INTERVAL HOUR TO SECOND)
Or forget about the INTERVAL type and just build a string you can CAST to TIMESTAMP, such as
CAST(TO_CHAR(datefield)||CAST(CAST(timefield AS FORMAT '999999') AS CHAR(6)) AS TIMESTAMP(0) FORMAT'yyyy/mm/ddhhmiss')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论