Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2012
    Posts
    12

    Question Unanswered: Odd Date Calculation

    Quick background: My employer uses a rather odd (to others, it seems) date range for our Fiscal months - the 22nd to the 21st of each month. For example, Fiscal February runs 01.22.2012 to 02.21.2012.

    Up until this point I've been able to use SYSDATE and simple functions to calculate dates in my reporting - they've just been the previous day, or previous 7 days, etc.

    Now I need to work on reports that have this dynamic date range, and I'm not sure where to start.

    What I'm looking for is something that will look at today's date (03.02.2012, for example), and logically determine that the current fiscal month (and thus date range for the report) is 02.22.2012 - 03.21.2012.

    Is this even possible?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is this even possible?
    I would write 2 functions.
    One function returns BEG_DATE & the other function returns END_DATE; based upon SYSDATE.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by anacedent View Post
    >Is this even possible?
    I would write 2 functions.
    One function returns BEG_DATE & the other function returns END_DATE; based upon SYSDATE.
    Well, here's where I'm going to show my ignorance.

    I've not used functions before, so I'm not sure how to proceed. The other thing is that this database is read-only for me - there's no way around that as it's a replicated data store from our primary billing system - are functions stored on the DB itself?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I've not used functions before,
    YES, you have used functions before.
    SYSDATE is an Oracle supplied function; which returns OS date/time in Oracle DATE format.

    PL/SQL Subprograms
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I've not used functions before,
    YES, you have used functions before.
    SYSDATE is an Oracle supplied function; which returns OS date/time in Oracle DATE format.

    PL/SQL Subprograms
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by anacedent View Post
    >I've not used functions before,
    YES, you have used functions before.
    SYSDATE is an Oracle supplied function; which returns OS date/time in Oracle DATE format.

    PL/SQL Subprograms
    I didn't realize that SYSDATE was considered a function; I stand corrected.

    However, I've never written my own functions before - I've only (apparently) used the built in ones.

    I've looked at the link that you've provided and I'm trying to make heads and tails of it, but I have to admit that I'm struggling. I'm not able to execute any of the examples provided in the documentation - I get error messages about not having write ability.

    I'm not a formally trained Oracle user (obviously), I can write SELECT's all day long that get me what I want, and I've never run into this need before now.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If your schema has read-only access, then you can not WRITE any function.
    I don't know how or if this could be done with plain SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Check with the DBA to see if
    1. You could be given write access to create a function. If you or the folks running the function don't have table write access, then the function couldn't write to a table either.) For that matter, a function can't write to a table if it's being used in a select statement. You could CALL it & put the output into a variable from within PL/SQL, but you can't
      Code:
      SELECT YourBegDateFunc, field2 ... FROM YourTable
      with a function that writes to a table. You'll get the ORA-14551 error. Or,
    2. He could create a couple of functions that return the current period start and end dates, based on sysdate. That way he would know that there's no writing to the tables taking place.
    Last edited by loquin; 03-02-12 at 16:06.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by N.B. View Post
    What I'm looking for is something that will look at today's date (03.02.2012, for example), and logically determine that the current fiscal month (and thus date range for the report) is 02.22.2012 - 03.21.2012.
    Code:
    SELECT add_months(trunc(sysdate, 'MONTH') + 20, -1) + 1 as start_of_fy, 
           trunc(sysdate, 'MONTH') + 20 as end_of_fy
    from dual

  10. #10
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by shammat View Post
    Code:
    SELECT add_months(trunc(sysdate, 'MONTH') + 20, -1) + 1 as start_of_fy, 
           trunc(sysdate, 'MONTH') + 20 as end_of_fy
    from dual
    This works, actually - at least for today. Unfortunately I can't really test it for the edge cases (moving from one month to another, or one year to another) at the moment - will it work for those, or will I have to wait and see?

    Also, when I supply a date to my query, I have to use this:

    Code:
    AND W.WFINDATE BETWEEN DATE '2012-02-22' AND DATE '2012-02-29'
    Will the default output from that select (22.02.2012 00:00:00) work, or will I need to format it using to_char ?

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by N.B. View Post
    This works, actually - at least for today. Unfortunately I can't really test it for the edge cases (moving from one month to another, or one year to another) at the moment - will it work for those, or will I have to wait and see?
    Why don't you try it? Just replace SYSDATE with the approriate "test date", e.g.
    Code:
    SELECT add_months(trunc(DATE '2012-08-15', 'MONTH') + 20, -1) + 1 as start_of_fy, 
           trunc(DATE '2012-08-15', 'MONTH') + 20 as end_of_fy
    from dual;
    Will the default output from that select (22.02.2012 00:00:00) work, or will I need to format it using to_char ?
    There is no such thing as a "default output". The output always depends on your tool. And your tool might be influenced by locale settings of the operating system.

    So if you want to be sure, do use to_char() with the approriate format mask.

    And I suggest you read the manual regarding date handling. All your questions are answered there.

  12. #12
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by shammat View Post
    Why don't you try it? Just replace SYSDATE with the approriate "test date", e.g.
    Code:
    SELECT add_months(trunc(DATE '2012-08-15', 'MONTH') + 20, -1) + 1 as start_of_fy, 
           trunc(DATE '2012-08-15', 'MONTH') + 20 as end_of_fy
    from dual;
    My apologies - it didn't occur to me that I could simply supply the date instead of SYSDATE and have it perform the operation. I have determined that it does break across the year just fine, however:

    Code:
    SELECT add_months(trunc(DATE '2012-02-25', 'MONTH') + 20, -1) + 1 as start_of_fy, 
           trunc(DATE '2012-02-25', 'MONTH') + 20 as end_of_fy
    Doesn't return 02.22.2012 - 03.21.2012, which would be the case by the 25th of February. In fact it doesn't roll forward to the next Fiscal range until the date is set to 03.01.2012, which is problematical.

    Quote Originally Posted by shammat View Post
    There is no such thing as a "default output". The output always depends on your tool. And your tool might be influenced by locale settings of the operating system.
    My experience with databases prior to Oracle (don't laugh) was Access, and Access requires that dates be submitted in a specific format otherwise they cannot be matched. Hence my question.

    Quote Originally Posted by shammat View Post
    And I suggest you read the manual regarding date handling. All your questions are answered there.
    Quite frankly, I don't have a manual for Oracle, and the software manual (for my client) is less than helpful. I'm an end-user on an Enterprise Oracle install, I have a data dictionary and Google at my disposal and that's it - the DBA's are not inclined to assist users with their queries, so I have to ask questions where I can't figure something out.

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by N.B. View Post
    Quite frankly, I don't have a manual for Oracle
    It's all online:
    http://www.oracle.com/pls/db112/homepage

    Here is the SQL Reference:
    http://docs.oracle.com/cd/B28359_01/...b28286/toc.htm

    Here is the part about date literals:
    http://docs.oracle.com/cd/B28359_01/...3.htm#BABGIGCJ

    And here is the list of all date functions:
    http://docs.oracle.com/cd/B28359_01/...001.htm#i88891

    Doesn't return 02.22.2012 - 03.21.2012, which would be the case by the 25th of February.
    You might try round() instead of trunc to get the desired behaviour:

    Code:
    SELECT add_months(round(DATE '2012-02-25', 'MONTH') + 20, -1) + 1 as start_of_fy, 
           round(DATE '2012-02-25', 'MONTH') + 20 as end_of_fy
    from dual;
    That returns 22.2.2012 to 21.3.2012
    Last edited by shammat; 03-02-12 at 18:45.

  14. #14
    Join Date
    Mar 2012
    Posts
    12
    Quote Originally Posted by shammat View Post
    It's all online:
    Oracle Database Online Documentation 11g Release 2 (11.2)

    Here is the SQL Reference:
    Contents

    Here is the part about date literals:
    Literals

    And here is the list of all date functions:
    About SQL Functions
    I appreciate those resources - I've bookmarked them for the future.

    For what it's worth, this is what I finally came up with and it works with all the test cases I can throw at it:

    Code:
    CASE
      WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) < 23
        THEN TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'), 'YYYYMM') || '22', 'YYYYMMDD')
      ELSE TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'YYYYMM') || '22', 'YYYYMMDD')
    END AS FISCALSTART,
    
    CASE
      WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) < 23
        THEN TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'YYYYMM') || '21', 'YYYYMMDD')
      ELSE TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM'), 'YYYYMM') || '21', 'YYYYMMDD')
    END AS FISCALEND,
    Each piece can be used as a sub-SELECT in the WHERE clause and return the date.

    I do appreciate all the help however - and this has been enlightening.

Posting Permissions

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