as400日期查询

发布于 2024-11-08 19:39:41 字数 168 浏览 0 评论 0原文

要在 as400 中查询从今天开始的最后 7 天，因为它将日期存储在 char 类型中，如何检索今天的结果，因为我尝试使用诸如

        where chardate >= char(days(curdate()) + 7)

但它仍然不起作用

原文

To query the last 7 days from today in as400 as it stores the dates in char type how to retrieve the results from today as i tried using such as

        where chardate >= char(days(curdate()) + 7)

but its still not working

分享到QQ

分享到微博

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

发布评论

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

天煞孤星 2024-11-15 19:39:41

答案分为两部分。第一个涉及 DB2 的这种特殊风格的日期数学。相当于 curdate()) + 7 的 DB2 表达式是当前日期 + 7 天。

第二个涉及将日期值转换为字符值（反之亦然），以便我们可以比较它们。在真正破解日期之前，我们需要知道 chardate 存储日期的格式。我们假设它采用 YYYY-MM-DD 格式。然后，您可以使用 char(current date + 7 days, iso) 来以相同的格式获取未来 7 天。

因此，根据这个假设，您的 where 语句将是

where chardate >= char(current date + 7 days, iso)

date 可以转换为几种标准日期格式：

ISO: YYYY-MM-DD
USA: MM /DD/YYYY
EUR：DD.MM.YYYY
JIS：YYYY-MM-DD

如果您的 chardate 位于不同的格式，您将需要使用 substr 进行一些相当繁琐的工作。例如，要将 YYYY-MM-DD 转换为 YYYYMMDD，您需要类似的内容

substr(char(current date, iso), 1, 4) ||
substr(char(current date, iso), 5, 2) ||
substr(char(current date, iso), 7, 2)

此方法的一个主要问题是格式不存储在“年月”中天”订单无法进行可靠比较。也就是说，12311969（即 MMDDYYYY）将比较大于 01012011，因为就数据库而言，您正在比较两个八位数字。（这就是为什么您几乎应该始终将日期存储在实际的 date 字段或 YYYYMMDD 或类似的正确排序格式中。）

我使用名为 idate，它提供 SQL 用户定义函数 (UDF) 来转换存储在 char 和 date 中的日期。数字字段转换为日期。请注意，此解决方案需要 RPG 编译器的可用性。

There are two parts to the answer. The first involves date math on this particular flavor of DB2. The DB2 expression equivalent to curdate()) + 7 is current date + 7 days.

The second involves converting date values to character values (or vice versa) so we can compare them. We need to know what format chardate stores dates in before we can really crack that one. Let's assume it's in YYYY-MM-DD format. Then you can use char(current date + 7 days, iso) to get seven days in the future in the same format.

So, with that assumption, your where statement would be

where chardate >= char(current date + 7 days, iso)

There are several standard date formats that date can convert to:

ISO: YYYY-MM-DD
USA: MM/DD/YYYY
EUR: DD.MM.YYYY
JIS: YYYY-MM-DD

If your chardate is in a different format, you will need to do some rather fiddly work with substr. For example, to convert YYYY-MM-DD to YYYYMMDD you'd need something like

substr(char(current date, iso), 1, 4) ||
substr(char(current date, iso), 5, 2) ||
substr(char(current date, iso), 7, 2)

A major problem with this method is that formats that aren't stored in "year month day" order can't be reliably compared. That is, 12311969 (i.e., MMDDYYYY) will compare as greater than 01012011, since as far as the database is concerned, you're comparing two eight-digit numbers. (That's why you should almost always store dates in actual date fields or in YYYYMMDD or similar properly ordered format.)

I've had great success using a free utility called idate, which provides SQL user-defined functions (UDFs) to convert dates stored in char & numeric fields into dates. Note that this solution requires the availability of an RPG compiler.

回复收藏 0 原文

尛丟丟 2024-11-15 19:39:41

从今天起的最后 7 天：

where 
date(substr(chardate,1,4) || '-' || 
substr(chardate,5,2) || '-' || 
substr(chardate,7,2)) between current date - 7 days and current date

要执行字符范围比较：

where
chardate between 
substr(char(current date - 7 days, iso),1,4) ||
substr(char(curernt date - 7 days, iso),6,2) ||
substr(char(current date - 7 days, iso),9,2)
and 
substr(char(current date, iso),1,4) ||
substr(char(current date, iso),6,2) ||
substr(char(current date, iso),9,2)

For the last 7 days from today:

where 
date(substr(chardate,1,4) || '-' || 
substr(chardate,5,2) || '-' || 
substr(chardate,7,2)) between current date - 7 days and current date

To perform a character range comparison:

where
chardate between 
substr(char(current date - 7 days, iso),1,4) ||
substr(char(curernt date - 7 days, iso),6,2) ||
substr(char(current date - 7 days, iso),9,2)
and 
substr(char(current date, iso),1,4) ||
substr(char(current date, iso),6,2) ||
substr(char(current date, iso),9,2)

回复收藏 0 原文

~没有更多了~