Date Functions in SQL for DB2 / AS400 / iSeries - IBM AS400

This is a discussion on Date Functions in SQL for DB2 / AS400 / iSeries - IBM AS400 ; 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() ...

+ Reply to Thread
Results 1 to 7 of 7

Thread: Date Functions in SQL for DB2 / AS400 / iSeries

  1. Date Functions in SQL for DB2 / AS400 / iSeries

    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.


  2. Re: Date Functions in SQL for DB2 / AS400 / iSeries

    On 2 Apr., 06:22, "travo" wrote:
    > 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
    >From SysIBM/SysDummy1


    Birgitta


  3. Re: Date Functions in SQL for DB2 / AS400 / iSeries

    > 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



  4. Re: Date Functions in SQL for DB2 / AS400 / iSeries


    > > 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

    --


  5. Re: Date Functions in SQL for DB2 / AS400 / iSeries

    On Apr 2, 10:28 am, RevDuane
    wrote:
    > > > 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


    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.


  6. Re: Date Functions in SQL for DB2 / AS400 / iSeries

    On Apr 2, 12:28 pm, RevDuane
    wrote:
    > > > 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
    >
    > --- 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


  7. Re: Date Functions in SQL for DB2 / AS400 / iSeries

    > 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


+ Reply to Thread