AS/400 >> SQL : retrieve the system date

by Seb » Thu, 16 Feb 2006 19:14:54 GMT

Hi all,

Little questions for you, specialists of AS400
is there any way to get the system date in SQL (not embedded) ?
the point would be to get something like
Select * from MyFile Where Mydate = SystemDate

Furthur i was wondering how the system deals with the system date.
Does it calculate it everyday using a dtaara, or something like this,
or uses it a system file ?

Thank you by advance for your answers.
Good day

AS/400 >> SQL : retrieve the system date

by walker.l2 » Thu, 16 Feb 2006 21:53:55 GMT

AS/400 >> SQL : retrieve the system date

by Jonathan Ball » Fri, 17 Feb 2006 02:33:00 GMT

There is a special register called current_date. If the Mydate column
in MyFile is a date data type, then you can code

selecct * from MyFile where Mydate = current_date

You can also code it 'current date' (no underscore) or 'localdate'.

If Mydate is not a date data type, you're going to have to perform a

AS/400 >> SQL : retrieve the system date

by Jonathan Ball » Fri, 17 Feb 2006 02:34:06 GMT

Oops - only one 'c' in 'select'...

AS/400 >> SQL : retrieve the system date

by Drew Dekreon » Wed, 22 Feb 2006 09:27:01 GMT

I seem to recall reading that curdate()/current_date is deprecated and
'current date' should always be used (for cross dbms compatibility, if
nothing else).

AS/400 >> SQL : retrieve the system date

by Jonathan Ball » Wed, 22 Feb 2006 10:02:30 GMT

The iSeries SQL Reference (v5r3) says concerning the curdate()
function: "Syntax alternatives: The CURRENT_DATE special register
should be used for maximal portability." As far as the form of the
special register 'current_date', it says that the form *with* the
underscore is what is in the SQL 1999 "core standard".

AS/400 >> SQL : retrieve the system date

by Drew Dekreon » Thu, 02 Mar 2006 08:25:35 GMT

Under v5r3, sql accepts curdate(), current_date, and current date
equally... I've never seen the underscore requirement before. Current
Time is valid as well, by the way (as is curtime() and current_time).

Similar Threads

1. system date vs job date


Looking ahead a bit.  We are thinking of doing our month end/year end
on Dec 30th.  We have 2 or 3 employees who might work on Sat 12/31,
but would like to do their work as of 1/1.  I wasn't at this company
last year.  

In the past, they have changed the sysval of QDATE to change the date
on their jobs/sessions, instead of using a CHGJOB.  I guess it's
worked fine in the past, but I think the CHGJOB would be better than
CHGSYSVAL??  Obviously, if we CHGSYSVAL, it eliminates the need to do
a CHGJOB on each session, it'll be the correct date automatically.  

Is there any "gotchas" if the CHGSYSVAL is used?  Like I said it makes
the need for CHGJOB unnecessary, but I wasn't sure if there was
anything else that would be affected.  When they were done working for
the day they would just do a CHGSYSVAL and change the date back to

George Applegate

2. retrieving write/last change date of flat-file dataset - AS/400

3. JDBC incorrectly retrieves date

Hi everyone,

Has anyone come across a problem where on Linux using DB2 9.1 Express-
C with the packaged jcc-JDBC driver that it fails correctly to parse a
returned date value? I'm simply calling


and it's giving me a date that's way off.

The reason that I find this bizarre is that I can see the correct date
value being transferred over the wire (localhost) using the Wireshark
packet-sniffer; also because it parses timestamp values correctly from
the same statement.

This is exposed in a unit-test which is run with a classpath that
includes the following files:


The result-set is returned as a cursor from a stored procedure which
reads from a temporary table. The value is read from a column in that
table declared as type DATE.

I'm sure I'm missing something pretty obvious so I thought I'd ask
first before getting to involved in rooting out the problem!



4. Retrieving table SQL text

5. Retrieving get_db_config information in SQL statement.


Using UDB 8.2 there is a procedure called get_db_config. What I would like
to do is to retrieve a certain value from the DB Config in a SQL Statement.

To give some more detail, I created a view which will show me my complete
bufferpool configuration. However, now with V8.2 I would like to show the
value from BUFFPAGE for the size of the bufferpool if the npages in
syscat.bufferpools is null.
Now how can I do this?

I thought on creating a function which would call the get_db_config
procecure but I do not know how to then get the result from the procedure in
the function to be able to return the result.

Anyone any ideas on this?


6. SQL Query to retrieve Server Hostname/IP address

7. Retrieve MS SQL data from iSeries


I am trying to connect to a MS SQL 2000 (or SQL 2005) Server to
retreive data from a database table and bring back into the iSeries for
processing.  This has to be a real time process and not a overnight
down load.  At the moment I am only interested in read access only.

The connection has to be initiated by the iSeries.



8. retrieving stored procedure result sets with embedded SQL