Date Functions in SQL for DB2 / AS400 / iSeries

[travo]

Hi, I'm kinda new to AS400, having been lounging in SQL Server land
for years. I'm writing SQL queries to generate data for a web app. How
does one perform date difference 'arithmetic' against date columns?

For example
SELECT CURDATE() - "30 Days"?

Any tips would be greatly appreciated.

[Hau...@sss-software.de]

Hi,

just remove the double qoutes!

Select Current_Date - 30 Days
>From SysIBM/SysDummy1

Birgitta

[travo]

> just remove the double qoutes!
> Select Current_Date - 30 Days
>

Thanks Birgitta, the quotes were just to surround the plain language
phrase that I had in place, sorry for the confusion - I did try your
technique though - just to be sure - and unfortunately I had no luck.

I did have more luck working with the JULIAN_DAY() function - it
allowed me to perform some day-based arithmetic on the columns I was
working with.

I'm still open for suggestions, but for now I've got things working.

Cheers,
Travis

[RevDuane]

Travis,

I have an SQLRPGLE program that is calculating a date 30 days in the
past. The full embedded statement I am using is:

C/EXEC SQL
C+ select (CURRENT_DATE - 30 DAYS) into :CvtDate
C+ from SYSIBM/SYSDUMMY1
D/END-EXEC

Seems to me there was some significance to the parenthesis around the
date. You might try that one.

Hope my $.02 is really worth it. :)

Duane

--

[Jonathan Ball]

On Apr 2, 10:28 am, RevDuane <member1...@nomx.sysadminforum.com>
wrote:

They worked for me in SQL Script Processor window with or without the
parentheses. I successfully ran all of the following and got the
correct results:

select current date - 5 years from sysibm.sysdummy1;

select current date - 3 months from sysibm.sysdummy1;

select current date - 30 days from sysibm.sysdummy1;

It could be the original poster is on a version/release of OS/400 that
doesn't support these.

[Elvis]

On Apr 2, 12:28 pm, RevDuane <member1...@nomx.sysadminforum.com>
wrote:

> --- Hide quoted text -
>
> - Show quoted text -

Duane, I don't think think you need to run the actual SELECT in
embedded SQL. Using VALUES clause should suffice, i.e.:

C/EXEC SQL
C+ VALUES(CURRENT_DATE - 30 DAYS) into :CvtDate
D/END-EXEC

It may save few MIPs of runtime.

Elvis

[Hau...@sss-software.de]

> C/EXEC SQL
> C+ VALUES(CURRENT_DATE - 30 DAYS) into :CvtDate
> D/END-EXEC

Instead of values(), also SET can be used:

C/Exec SQL Set :CvtDate = Current_Date - 30 Days
C/End-Exec

I assume, because the orignal poster has to use the scalar function
Julian_Day(), the date stored in the database file was no real date,
but a character representation of a date. To convert a character
representation into a date also the scalar function DATE() can be
used.

Birgitta