Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: need help on custom date function

    I want to create a custom function for DB2 Previous Fiscal Calendar .If someone can help me Appreciate it
    for example

    if Daterange = 'Previous Calendar Quarter' and MONTH(CURRENT DATE) in (1,4,7,10) THEN set V_RETURN_DATE = CURRENT DATE - 3 MONTHS - DAY(CURRENT DATE) DAYS + 1 DAY; end if;
    if Daterange = 'Previous Calendar Quarter' and MONTH(CURRENT DATE) in (2,5,8,11) THEN set V_RETURN_DATE = CURRENT DATE - 4 MONTHS - DAY(CURRENT DATE) DAYS + 1 DAY; end if;
    if Daterange = 'Previous Calendar Quarter' and MONTH(CURRENT DATE) in (3,6,9,12) THEN set V_RETURN_DATE = CURRENT DATE - 5 MONTHS - DAY(CURRENT DATE) DAYS + 1 DAY; end if;

    This example is for Previous Calendar Quarter i am looking for Previous Fiscal quarter .I need help to change the code to Previous Fiscal Quarter
    Last edited by denverdba; 09-26-13 at 10:25.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    pg 182 of the manual

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Be carefull the sequence of DAYs and MONTHs calculation.

    Example : Replaced "CURRENT DATE" by "CURRENT_DATE_" for test purpose.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CURRENT_DATE_
         , CASE
           WHEN MONTH(CURRENT_DATE_) in (1,4,7,10) THEN
                CURRENT_DATE_ - 3 MONTHS - DAY(CURRENT_DATE_) DAYS + 1 DAY
           WHEN MONTH(CURRENT_DATE_) in (2,5,8,11) THEN
                CURRENT_DATE_ - 4 MONTHS - DAY(CURRENT_DATE_) DAYS + 1 DAY
           END  AS your_V_RETURN_DATE
         , CASE
           WHEN MONTH(CURRENT_DATE_) in (1,4,7,10) THEN
                CURRENT_DATE_ - DAY(CURRENT_DATE_) DAYS + 1 DAY - 3 MONTHS
           WHEN MONTH(CURRENT_DATE_) in (2,5,8,11) THEN
                CURRENT_DATE_ - DAY(CURRENT_DATE_) DAYS + 1 DAY - 4 MONTHS
           END  AS revised_V_RETURN_DATE
     FROM  (VALUES
               DATE('2013-01-01')
             , DATE('2013-01-31')
             , DATE('2013-04-01')
             , DATE('2013-04-30')
             , DATE('2013-07-31')
             , DATE('2013-08-31')
             , DATE('2013-10-31')
           ) s(CURRENT_DATE_)
    ;
    ------------------------------------------------------------------------------
    
    CURRENT_DATE_ YOUR_V_RETURN_DATE REVISED_V_RETURN_DATE
    ------------- ------------------ ---------------------
    2013-01-01    2012-10-01         2012-10-01           
    2013-01-31    2012-10-01         2012-10-01           
    2013-04-01    2013-01-01         2013-01-01           
    2013-04-30    2013-01-01         2013-01-01           
    2013-07-31    2013-03-31         2013-04-01           
    2013-08-31    2013-03-31         2013-04-01           
    2013-10-31    2013-07-01         2013-07-01           
    
      7 record(s) selected.

Posting Permissions

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