SQL query with Current Date in As400

Funny formats, often numeric, are common on the AS/400 / DB2, I'm afraid.  So you're going to have some formatting fun.

About 3 solutions to my particular format situation (for one customer) are at https://www.experts-exchange.com/questions/22987515/iSeries-DB2-CURRENT-TIMESTAMP-Decimal-14-in-SQL.html

Perhaps you can adapt one of those ideas.  I do think that using CURRENT_TIMESTAMP as the basis of your calculation is probably the way to go.

Daniel, your examples are exactly what my first thoughts were as well.  Another alternative to having to alter the integer values so they add up correctly is you can convert the Year(), Month(), and Day() returns to be strings and concatenate to same format like '20080101' and then just convert your Date field to string during comparison as well.

In the distant past, the AS/400 database didn't support Date, Time, and Timestamp fields.  Then there was a period when the database supported them, but the legacy programming languages (RPG, CL), didn't.  As a result there are a lot of legacy tables that contain dates, times, and timestamps stored in numeric format.  

I still see legacy programmers that "grew up" in the "pre-date" era that create numeric fields for these data types.

It is a constant pain.

Create a set of functions to handle the particular brand of numeric -> date/time/timestamp conversions required in your system (and add them to your toolkit for the next shop with similar problems!).

Hi aozdamar,

within SQL you have the Currendate like
Where MyDate = Current Date
But the format = YY/MM/DD

You can change that by  CHAR(current DATE, ISO)
Then the result will be 2008-08-06 but you need some substringing
to get your DB-date into this format. so it's more easy to pass the date
from CL to the Management query
alter Date = DateNow into Date = &DateNow

And write a CL to retrieve today's date from the system value
See Snippet
Regards,
Murph

PGM                                                         
 
DCL        VAR(&SYSDATE) TYPE(*CHAR) LEN(20)                
DCL        VAR(&DATENOW) TYPE(*CHAR) LEN(8)                 
                                                            
RTVSYSVAL  SYSVAL(QDATETIME) RTNVAR(&SYSDATE)               
                                                            
CHGVAR     VAR(&DATENOW) VALUE(%SST(&SYSDATE 1 8))          
                                                            
STRQMQRY   QMQRY(MyLib/MyQMQ) SETVAR((DATENOW &DATENOW))
                                                            
ENDPGM                                                      

Select allOpen in new window

Hi Gary,
Yep your right:
I still see legacy programmers that "grew up" in the "pre-date" era that create numeric fields for these data types.
After al these years I use the numeric date fields again, after loads of troubles in diferent conversion jobs.

Don't try to learn an old monkey new tricks.
Murphey

If you take murphey2 suggestion, you can then use translate to strip out the hyphens (-).  

TRANSLATE(CHAR(current DATE, ISO), '-', '')

Select allOpen in new window

Hi aozdamar,

So with help from mwvias1 the SQL should work like this:

SELECT 
xxxxxx
,
 yyyyy
,
 zzzzzz 
FROM AAAA
/
BBBBB           

                        
  WHERE 

qqqqq  
in (

'101'
, 





'501'
)

    AND 

char(Date)
 
=
 
TRANSLATE
(
CHAR
(
current DATE
,
 ISO
),
 
'-'
,
 
''
)
)

Select allOpen in new window

or

SELECT 
xxxxxx
,
 yyyyy
,
 zzzzzz 
FROM AAAA
/
BBBBB           

                        
  WHERE 
(
qqqqqq  
=
 
'101'
)
 OR 
(
qqqqqq  
=
 
'501'
)

    AND 

char(Date)
 
=
 
TRANSLATE
(
CHAR
(
current DATE
,
 ISO
),
 
'-'
,
 
''
)
)

Regards,
Murph

Select allOpen in new window

BTW aozdamar,

"This query will be run everyday"
Can this also be Every night?
in that case non of these options will work unless you are sure the QSL is executed before 0:00 h.
If it's a part of the Nightly batch, you better take the JobDate instead of the system date,
This jobdate can be retrieved in CL by RTVJOBD.

An other option that will work is:

SELECT xxxxxx, yyyyy, zzzzzz FROM AAAA/BBBBB                                  
  WHERE (qqqqqq  = '101') OR (qqqqqq  = '501')
    AND Date in (Select MAX(Date) FROM AAAA/BBBBB)

This allways will select the records equal to the highest (newest) date in the table, but if there was no record inserted during 1 day, the SQL will make a selection from the last date found.

Regards,
Murph

Hello guys,

First of all thanks so much for your suggestions. I am very new to AS400 stuff and the sql statement I have posted is taken from the a menu called SQL view in "Work with Query Manager Queries".

This query will run every 10 minutes. Because I have no idea how to set and run this query automatically in AS 400 I have created a script that runs the IBM client access and runs the query in windows as a scheduled job.

I want to try the solution that murphy2 suggested. However I don't know how. I mean should I copy and paste  the below code and paste in to the screen that comes with STRSQL command ?

I might be asking a funny question but as I said I am very new to AS 400 stuff. Just not to deal with AS400 I even tried an ODBC connection with MS access and create the query there, however everytime I need to run the query it asks for username and pwd to connect to DB2...


<pre class="prettyprint">SELECT xxxxxx, yyyyy, zzzzzz FROM AAAA/BBBBB                                  
  WHERE qqqqq  in ('101', '501')
   AND char(Date) = TRANSLATE(CHAR(current DATE, ISO), '-', ''))</pre>or

<pre class="prettyprint">SELECT xxxxxx, yyyyy, zzzzzz FROM AAAA/BBBBB                                  
  WHERE (qqqqqq  = '101') OR (qqqqqq  = '501')
   AND char(Date) = TRANSLATE(CHAR(current DATE, ISO), '-', ''))

In the case you describe, I would go for the other solution:

SELECT xxxxxx, yyyyy, zzzzzz                                        
    FROM AAAA/BBBBB                                
    WHERE qqqqqq  in ('101', '501')
           AND Date  = &DateNow

Put this in a source file and create the query with CRTQMQRY
then make a source member from the type CLP
and copy the next snippet into it change the 'MyLib/MyQMQ'  into your query name and compile.

Now you can run the job by calling your CL program.
if you like you can start this daily by the as/400 scedular and extend the CL program with a loop so that it runs every 10 minutes until a specific time.

But please explain what the purpose is of this query, maybe there are more solutions (more easy)

PGM                                                         
 
DCL        VAR(&SYSDATE) TYPE(*CHAR) LEN(20)                
DCL        VAR(&DATENOW) TYPE(*CHAR) LEN(8)                 
 
                                                            
RTVSYSVAL  SYSVAL(QDATETIME) RTNVAR(&SYSDATE)               
                                                            
CHGVAR     VAR(&DATENOW) VALUE(%SST(&SYSDATE 1 8))          
                                                            
STRQMQRY   QMQRY(MyLib/MyQMQ) SETVAR((DATENOW &DATENOW))
 
                                                            
ENDPGM     

Select allOpen in new window

This query gets the goods in data of the warehouse from our system. I will send this data to our outsource Warehouse companies ftp server. So that they will make the same entries to their system. Some kind of an EDI...

So it must be stored in a file?
Then send to the warehouse using FTP?

You FTP it from a PC or from the AS/400?

I advise to keep it on 1 system
Create a AS/400 solution that select and send it to the warehouse
-or-
Download it into a PC file every 10 minutes via
AS/400 File transfer or ODBC (I can provide you an example for both, but I advise filetransfer)
-or-
Hire me to develope the tool for you.... :)

Regards,
Murph




&nbsp;

Hi Murphey2,

I wish I have the opportunity to hire you :)

Download it into a PC file every 10 minutes via
AS/400 File transfer or ODBC advice sounds nice. Because I will be sending the files via a thirdparty FTP software called AutoFTP. I would like to ave a copy of the files on my windows system just incase of an error in transfer. I f you can provide an example that would be great actually.

B.regards,

Altug

I don't know if I will be able to do this but I give you the points for the effort.

B.Regards,