  |
|
|
|
||||||||
 |
 |
|
|
 |
||
|
More Query/400 and Date Selection Techniques Dear Readers: I know many of you are stuck with Query/400, in spite of the facts that IBM apparently never got around to finishing it and that there is an abundance of better query packages on the market. So I enjoy running the tips I receive from Bob Ellsworth, the man who makes Query/400 go where it has never gone before. Today I am pleased to present some alternatives to one of Bob's recent tips. I hope they may be of help in the daily struggle. --Ted 
Hey, Ted: I am surprised you haven't suggested a much easier solution than the one Bob presented. Use an SQL view! The following command creates a view that matches Bob's example. CREATE VIEW xxx/TODAYSDATE as SELECT CURRENT DATE as TODAY, CURRENT DATE-1 DAY as YESTRDY, CURRENT DATE-7 DAYS as LASTWK, CURRENT DATE+3 MONTHS as NEXT3M FROM QSYS2/QSQPTABL --Yannick Hey, Ted: Here's a trick I use for dates in SQL. I create a lot of SQL statements in a source library and then use RUNSQLSTM to execute so that's the context of the insert. You only need to run this once to create a view. The DROP statement is for making changes.
drop view bobsqrys/qtoday;
-- you can format to suit
create view bobsqrys/qtoday as
select ((year(curdate())*10000)+
(month(curdate())*100)+
(day(curdate()))) as today,
((year(curdate()-1 day)*10000)+
(month(curdate()-1 day)*100)+
(day(curdate()-1 day))) as ystrday,
((year(curdate()-7 day)*10000)+
(month(curdate()-7 day)*100)+
(day(curdate()-7 day))) as lstweek,
((year(curdate()-3 month)*10000)+
(month(curdate()-3 month)*100)+
(day(curdate()-3 month))) as thremth
from qsys2/qsqptabl
--Bob Hey, Ted: Why do you need the date file and the join logic? Use the current(date) function. Calculate other dates based on today and use that for selection. Here are some of the date expressions from a query I created several years ago to demonstrate query date functions.
Field Expression Column Heading
========= ================================ ==================
YEAR_AGO current(date) - 1 year One year ago
TOMORROW char(current(date) + 1 day, iso) Tomorrow
FIRST current(date) + 1 day - First day of month
day(current(date)) day
LAST current(date) + 1 month - Last day of month
day(current(date)) day
Thanks to all of you for these tips. I feel confident that many readers will find them helpful. Bob's technique would still be needed to handle dates of unsupported types, such as the Julian format. Joining to a file can also be cleaner than including a lot of messy calculations. I have joined to a file of dates many times and have found that technique very helpful. --Ted
|
Editors
Contact the Editors |
| Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved. |
GST
