Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Norway
    Posts
    53

    Unanswered: In need of Scalar Function example (dates and years)

    I'm trying to write a generic filter condition so I can run a query any time and the returned data is between this years last month and 12 months back.

    What I've achieved so far is to get data from 12 months back, based upon the current date.

    where
    mytimestampcol between
    current_date - 12 months and current_date - 1 months;

    ---

    I've also tried the mathemathical approach with month() and year() functions, although this gives me data from 01.01.05 to 31.03.06 whereas I wanted 01.04.05 to 31.03.06 (if I ran the query today)

    ---

    Thank you in advance

    with Regards
    Filip Poverud

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    for Db2 for z/OS this should work:


    ... BETWEEN
    ADD_MONTHS(LAST_DAY(CURRENT DATE),-13) + 1 DAY
    AND
    ADD_MONTHS(LAST_DAY(CURRENT DATE),-1)

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Code:
    select * from emp
    where hiredate >=
    cast(year(current_date -12 months)as char(4)) || '-' ||
    cast(cast(month(current_date - 12 months) as decimal(2,0) ) as char(2)) || '-' || '01'  
    and  hiredate < 
    cast(year(current_date)as char(4)) || '-' ||
    cast(cast(month(current_date) as decimal(2,0) ) as char(2)) || '-' || '01'
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Oct 2004
    Location
    Norway
    Posts
    53
    umayer: thx, I haven't found last_day() on LUW though but its working like a charm on z/OS

    Marcus: thx, although it doesn't look good (my objective opinion) it's working and thats what I wanted to achieve I had to wrap the predicate with date() as it is of type timestamp.

    with Regards
    Filip Poverud

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Filip,

    See if you 'like' this

    select * from syscat.tables where date(create_time) between
    (current date - (day(current date)) days + 1 day - 12 months) and
    (current date - day(current date) days)

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •