如何将小数日期转换为 where 语句中的日期字段？

发布于 2024-12-11 18:56:01 字数 373 浏览 2 评论 0原文

我有一个十进制日期 (YYYYMMDD) 的字段。我需要找到所有一年或更短历史的记录。既然约会如此简单，我尝试这样做：

WHERE DATE(SUBSTR(CHAR(A.CPADDT),1,4) CONCAT '-' CONCAT 
      SUBSTR(CHAR(A.CPADDT),5,2) CONCAT '-' CONCAT
      SUBSTR(CHAR(A.CPADDT),7,2)) > CURRENT_DATE - 1 YEAR

这在 SELECT 部分效果很好。当我把它放在 WHERE 中时我收到“涉及字段 *N 的选择错误”。

对我做错了什么有什么想法吗？

原文

I have a field that is a decimal date (YYYYMMDD). I need to find all
records that are a year or less old. Since date are so easy, I am
trying to do:

WHERE DATE(SUBSTR(CHAR(A.CPADDT),1,4) CONCAT '-' CONCAT 
      SUBSTR(CHAR(A.CPADDT),5,2) CONCAT '-' CONCAT
      SUBSTR(CHAR(A.CPADDT),7,2)) > CURRENT_DATE - 1 YEAR

This works great in the SELECT portion. When I put it in the WHERE
section I get "Selection error involving field *N."

Any ideas of what I am doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

呆° 2024-12-18 18:56:01

幸运的是，YYYYMMDD 最终遵循自然顺序。因此，与其解析记录，为什么不直接“格式化”一年前的日期并与之进行比较呢？

目前尚不清楚您从哪里调用它，以及您是否可以在那里提供逻辑，但如果不能，并且这是在存储过程中，您始终可以将逻辑放在那里。基本上你想要有类似（伪代码）的东西：

CUTOFF = CURRENT_DATE - 1 YEAR
CUTOFF_NUMERIC = CUTOFF.YEAR * 10000 + CUTOFF.MONTH * 100 + CUTOFF.DAY
SELECT ... FROM A WHERE A.CPADDT > CUTOFF_NUMERIC

抱歉无法给你实际代码 - 我不熟悉这种 SQL 方言，而且我离无论如何，SQL 专家。希望这足以让你继续前进。请注意，这对性能也有帮助 - 如果您在 CPADDT 上有索引，那么此查询可以轻松使用它，而您最初的尝试可能无法使用它。即使您没有索引，简单的数字比较也可能比所有格式化和解析更便宜。

Fortunately, YYYYMMDD ends up following a natural order. So rather than parsing the records, why not just "format" the date from a year ago, and make a comparison with that?

It's unclear exactly where you're calling this from, and whether you could provide the logic there, but if not, and this is within a stored procedure, you can always put the logic there. Basically you want to have something like (pseudo-code):

CUTOFF = CURRENT_DATE - 1 YEAR
CUTOFF_NUMERIC = CUTOFF.YEAR * 10000 + CUTOFF.MONTH * 100 + CUTOFF.DAY
SELECT ... FROM A WHERE A.CPADDT > CUTOFF_NUMERIC

Apologies for not being able to give you the actual code - I'm not familiar with this dialect of SQL, and I'm far from a SQL expert anyway. Hopefully that's enough of a suggestion to get you going though. Note that this should help in terms of the performance, too - if you have an index on CPADDT then this query can use it easily, whereas your original attempt probably couldn't. Even if you don't have an index, a simple numeric comparison is likely to be cheaper than all of that formatting and parsing.

回复收藏 0 原文

≈。彩虹 2024-12-18 18:56:01

涉及字段 *N 的选择错误。 表示表格中的日期无效。

检查作业日志中是否有 CPD4019。原因将包括错误日期的相对记录编号。

Message . . . . :   Select or omit error on field &10 member &1.            
Cause . . . . . :   A select or omit error occurred in record &5, record    
  format &7, member number &8 of file &2 in library &3, because of condition
  &6 of the following conditions:

您还可以像这样内联使用 Jon Skeet 的解决方案，

WHERE A.CPADDT > YEAR(CURRENT_DATE - 1 YEAR) * 10000                
+ MONTH(CURRENT_DATE - 1 YEAR) * 100 + DAY(CURRENT_DATE - 1 YEAR)

除了其他好处之外，根本不必担心日期转换错误。

Selection error involving field *N. indicates you have an invalid date in your table.

Check the job log for a CPD4019. The cause will include the relative record number of the bad date.

Message . . . . :   Select or omit error on field &10 member &1.            
Cause . . . . . :   A select or omit error occurred in record &5, record    
  format &7, member number &8 of file &2 in library &3, because of condition
  &6 of the following conditions:

You can also use the Jon Skeet's solution inline like so

WHERE A.CPADDT > YEAR(CURRENT_DATE - 1 YEAR) * 10000                
+ MONTH(CURRENT_DATE - 1 YEAR) * 100 + DAY(CURRENT_DATE - 1 YEAR)

and not have to worry about date conversion errors at all in addition to the other benefits.

回复收藏 0 原文

哭泣的笑容 2024-12-18 18:56:01

前面两个答案都很好。事实上，我是一个糟糕的程序员，并没有 100% 确定数据是有效的。有一些不应该有的“零日期”。当我从查询中删除这些内容后，它就可以工作了。

A.CPADDT <> 0 AND DATE(SUBSTR(DIGITS(A.CPADDT),1,4) CONCAT '-' CONCAT   
    SUBSTR(DIGITS(A.CPADDT),5,2) CONCAT '-' CONCAT        
    SUBSTR(DIGITS(A.CPADDT),7,2)) > CURRENT_DATE - 1 YEAR

在这种特殊情况下，如果有人想查看其付款状态，我们仅包含用于自我查找的数据。我可能还没有完成这个工作，因为我确信用户输入没有任何日期验证（该程序可能已有 15 年以上的历史）。

Both of the previous answers were good. In reality, I was a bad programmer and didn't make 100% sure the data was valid. There were a handful of "zero dates" where there isn't supposed to be any. After I removed those from the query it works.

A.CPADDT <> 0 AND DATE(SUBSTR(DIGITS(A.CPADDT),1,4) CONCAT '-' CONCAT   
    SUBSTR(DIGITS(A.CPADDT),5,2) CONCAT '-' CONCAT        
    SUBSTR(DIGITS(A.CPADDT),7,2)) > CURRENT_DATE - 1 YEAR

In this particular case, we are only including the data for self look-up if a person wants to see the status of their payment. I am probably not done with this as I am sure that there isn't any date validation on user input (the program is probably 15+ years old).

回复收藏 0 原文

~没有更多了~