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.

[H***@sss-software.de]

Post by 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.

Hi,

just remove the double qoutes!

Select Current_Date - 30 Days

Post by travo
From SysIBM/SysDummy1

Birgitta

[travo]

Post by H***@sss-software.de
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]

Post by travo

Post by H***@sss-software.de
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

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]

Post by RevDuane

Post by travo

Post by H***@sss-software.de
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

Travis,
I have an SQLRPGLE program that is calculating a date 30 days in the
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

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]

Post by RevDuane

Post by travo

Post by H***@sss-software.de
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

Travis,
I have an SQLRPGLE program that is calculating a date 30 days in the
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
--- 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

[H***@sss-software.de]

Post by RevDuane
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