SQL 当前日期 (AS400)

发布于 2024-10-20 09:26:43 字数 301 浏览 6 评论 0原文

我正在尝试从 AS400 数据库中选择具有当前日期（格式为 MMDDYY）的记录。

这就是我打算做的：

SELECT * FROM tableName WHERE $DATE='030411'

我尝试了这些组合，但没有运气：

SELECT * FROM tableName WHERE $DATE='SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')'

有什么建议吗？

原文

I am trying to select the records from an AS400 database that have a current date (in format MMDDYY).

This is what I am planning to do:

SELECT * FROM tableName WHERE $DATE='030411'

I tried combinations of this but with no luck:

SELECT * FROM tableName WHERE $DATE='SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '')'

Any suggestions?

分享到QQ

分享到微博

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

发布评论

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

活泼老夫 2024-10-27 09:26:43

试试这个：

SELECT * FROM tableName WHERE $DATE=
   substring(cast(current date as char(10)),6,2) || 
   substring(cast(current date as char(10)),9,2) || 
   substring(cast(current date as char(10)),3,2)

您可以看到这个表达式返回什么值，如下所示：

select substring(cast(current date as char(10)),6,2) || 
   substring(cast(current date as char(10)),9,2) || 
   substring(cast(current date as char(10)),3,2)    
from sysibm.sysdummy1

您必须使用 sysibm.sysdummy1 ，因为 AS/400（iSeries、System i 等）上的 SQL 不允许您这样做SELECT 凭空产生值。

另请注意，当前日期 可能会以不同的格式返回日期，具体取决于 SQL 日期格式。此代码预计采用 *ISO 格式 (YYYY-MM-DD)。

以下是我用来验证此例程的一些 SQL 语句。

create table dmclib.test2 ( $DATE decimal(6,0) ) ;

insert into dmclib.test2 values   
  (010111), (010211), (031011) ;

SELECT * FROM dmclib.test2                         
where $DATE =                                      
   substring(cast(current date as char(10)),6,2) ||
   substring(cast(current date as char(10)),9,2) ||
   substring(cast(current date as char(10)),3,2) ;

这是我得到的回复：

....+...                        
  $DATE                         
 31,011                         
********  End of data  ********

Try this:

SELECT * FROM tableName WHERE $DATE=
   substring(cast(current date as char(10)),6,2) || 
   substring(cast(current date as char(10)),9,2) || 
   substring(cast(current date as char(10)),3,2)

You can see what value this expression brings back like this:

select substring(cast(current date as char(10)),6,2) || 
   substring(cast(current date as char(10)),9,2) || 
   substring(cast(current date as char(10)),3,2)    
from sysibm.sysdummy1

You have to use sysibm.sysdummy1 since SQL on an AS/400 (iSeries, System i, etc.) doesn't let you SELECT values out of thin air.

Also note that current date might bring the date back in a different format, depending on the SQL date format. This code is expecting it to be in *ISO format (YYYY-MM-DD).

Here are some SQL statements that I used to validate this routine.

create table dmclib.test2 ( $DATE decimal(6,0) ) ;

insert into dmclib.test2 values   
  (010111), (010211), (031011) ;

SELECT * FROM dmclib.test2                         
where $DATE =                                      
   substring(cast(current date as char(10)),6,2) ||
   substring(cast(current date as char(10)),9,2) ||
   substring(cast(current date as char(10)),3,2) ;

Here's what I got back:

....+...                        
  $DATE                         
 31,011                         
********  End of data  ********

回复收藏 0 原文

青柠芒果 2024-10-27 09:26:43

insert( replace( char( current_date, usa ) , '/', '' ) , 5, 2, '')

上面使用 CHAR 转换标量函数生成当前日期的 *USA 格式的字符串表示形式 [即 MM/DD/YYYY]，然后替换 '/使用 REPLACE 标量函数将字符串 MM/DD/YYYY 中带有空字符串的 ' 字符变为 MMDDYYYY，然后插入空字符串从位置五开始[即 MMDDYYYY 中 YYYY 的第一个数字]，同时删除该字符串的两个字节[即“插入”实际上替换了第五和第六个字节，或根据插入标量上的参数，使用 INSERT 标量函数从第五 (5) 开始插入两 (2) 个字节，并带有空字符串]，从而生成所需的字符串结果 MMDDYY [其中最后两个字节 YY 是 MM/ 中表示为 YYYY 的数字的 10**1 和 10**0 部分DD/YYYY]。

问候，查克

insert( replace( char( current_date, usa ) , '/', '' ) , 5, 2, '')

The above generates the character string representation of the *USA format [i.e. MM/DD/YYYY] for the Current Date using the CHAR cast scalar function, then replaces the '/' character in the string MM/DD/YYYY with the null string to become MMDDYYYY using the REPLACE scalar function, and then inserts the null string starting as position five [i.e. the first digit of YYYY in MMDDYYYY] while removing two bytes of that string [i.e. the "insert" is actually replacing the fifth and sixth bytes, or per the arguments on the insert scalar from the fifth (5) for two (2) bytes, with the null string] using the INSERT scalar function, thus producing the desired result of the string MMDDYY [where the last two bytes YY are the 10**1 and 10**0 portions of the number denoted as YYYY in MM/DD/YYYY].