将日期转换放入where子句db2 sql [英] Put date conversion in where clause db2 sql

问题描述

我有以下查询。我正在计算 MYDATE 列，它是 DECIMAL（8，0）数据类型，它以数字形式存储datea格式为YYYYMMDD，例如20191107。

I have the following query. And I am calculating on MYDATE column which is a DECIMAL(8, 0) data type which stores datea in numeric form in YYYYMMDD format like this 20191107.

SELECT DECIMAL((TO_CHAR(TO_DATE(CAST(CAST(MYDATE AS INT) AS VARCHAR(8)), 'yyyymmdd') - 3 MONTHS, 'yyyymmdd')), 8, 0) FROM PDF_TABLE;

工作正常。但是，当我在 WHERE 子句中使用上述转换时，查询不会返回任何内容。

It works fine. However, when I use the above conversion in a WHERE clause like below, query does not return anything.

SELECT * FROM PDF_TABLE WHERE MYDATE = DECIMAL((TO_CHAR(TO_DATE(CAST(CAST(MYDATE AS INT) AS VARCHAR(8)), 'yyyymmdd') - 3 MONTHS, 'yyyymmdd')), 8, 0);

示例

以上是数据。现在，我在以下查询的 WHERE 子句中将日期20200601作为参数传递，它应从20200601减去3个月后返回下一行。

Above is the data as is. Now I pass the date 20200601 as parameter in the WHERE clause of the following query, it should return the following row after subtracting 3 months from 20200601.

SELECT * FROM PDF_TABLE WHERE MYDATE = DECIMAL((TO_CHAR(TO_DATE(CAST(CAST(MYDATE AS INT) AS VARCHAR(8)), 'yyyymmdd') - 3 MONTHS, 'yyyymmdd')), 8, 0);

推荐答案

怎么可能返回任何东西？

How could it possibly return anything?

MYDATE = DECIMAL（（TO_CHAR（TO_DATE（CAST（CAST（MYDATE AS INT）AS AS VARCHAR（8）），'yyyymmdd'））-3个月

放弃转换，您要询问 MYDATE = MYDATE-3个月

这永远不会相等...也许您忘了将内部 MYDATE 更改为> CURRENT_DATE 还是什么？这也需要转换。.

That's never going to be equal... Perhaps you forgot to change the inner MYDATE to CURRENT_DATE or something? That would also require the conversion to change..

我个人使用用户定义的函数进行此类转换；自己编写或下载Alan Campin的免费 iDate 源

Personally, I use a user defined function for this type of conversion; either write it yourself or download Alan Campin's free iDate source

WHERE MYDATE = ConvertToIDate（CCURRENT_DATE-3 months，'* CCYMD'）;

这篇关于将日期转换放入where子句db2 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！