Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: converting string date value in pp-yy format

    Hello,

    Our Oracle finance system has a system string format of pp-yy for it's date that our users enter for their report parameters. I'm trying to convert a string date format in pp-yy format (10-11) to a format to where I can have users enter a parameter value in the similar format where it returns the current year and previous years' data.

    I tried using to_date to either a mm/yy or mm/yyyy format and I get an ORA-01843 - not a valid input string, which makes sense since I have no day if I try using a mm/dd/yyyy format. But, I also get ORA-01840 - not a valid month if I try the mm/yy format.

    I tried doing a substring of the period (position 1) and year (position 4) and putting them back together as numbers or dates, but no luck.

    I'm stuck as I need to get this converted to our BI tool for reporting. Any ideas on converting this?
    Thanks in advance.

    Neal

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm trying to convert a string date format in pp-yy format (10-11)
    what is "pp" & what is valid range of values?

    >I'm stuck as I need to get this converted to our BI tool for reporting
    you have a string. to which datatype is the result of this desired conversion?
    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
    Sep 2011
    Posts
    5
    Quote Originally Posted by anacedent View Post
    >I'm trying to convert a string date format in pp-yy format (10-11)
    what is "pp" & what is valid range of values?




    >I'm stuck as I need to get this converted to our BI tool for reporting
    you have a string. to which datatype is the result of this desired conversion?
    pp in the original format would be the same as mm in Oracle.

    The goal is either number or date. In the report, I'm setting up a parameter to where the user is to enter the value - such as 09-11 (Sept 2011) and it has to be 09-11. But, when they enter the value, it brings back current year and previous years' data.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The goal is either number or date. In the report, I'm setting up a parameter to where the user is to enter the value - such as 09-11 (Sept 2011) and it has to be 09-11.
    >But, when they enter the value, it brings back current year and previous years' data.
    You have a mystery & we have no clues.
    You report a problem 100% devoid of context or specifics.
    So what do you expect or desire from here?
    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
    Sep 2011
    Posts
    5
    Quote Originally Posted by anacedent View Post
    >The goal is either number or date. In the report, I'm setting up a parameter to where the user is to enter the value - such as 09-11 (Sept 2011) and it has to be 09-11.
    >But, when they enter the value, it brings back current year and previous years' data.
    You have a mystery & we have no clues.
    You report a problem 100% devoid of context or specifics.
    So what do you expect or desire from here?
    What I am seeking is a way to convert the string that is in pp-yy (or mm-yy) string format to either a numeric or date format. I've tried separating the value to mm only using a substring. And I've also tried separating the last two numbers (yy) as a separate value. Then the result is concatenating the separated values as into a number or a date to run a report from. I hope that's more clear.

    In my reporting tool (Business Objects), I can use Oracle syntax to generate the parameter.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> select to_number(substr('10-11',1,2)||substr('10-11',4,2)) from dual;
    
    TO_NUMBER(SUBSTR('10-11',1,2)||SUBSTR('10-11',4,2))
    ---------------------------------------------------
    					       1011
    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.

  7. #7
    Join Date
    Sep 2011
    Posts
    5
    Quote Originally Posted by anacedent View Post
    Code:
    SQL> select to_number(substr('10-11',1,2)||substr('10-11',4,2)) from dual;
    
    TO_NUMBER(SUBSTR('10-11',1,2)||SUBSTR('10-11',4,2))
    ---------------------------------------------------
    					       1011
    I'll try that and see what kind of results I get. Thanks for your help!

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Select to_date('10-11','mm-rr') from dual;

    to_date('
    ---------
    01-oct-11
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Sep 2011
    Posts
    5
    Quote Originally Posted by beilstwh View Post
    Select to_date('10-11','mm-rr') from dual;

    to_date('
    ---------
    01-oct-11
    I tried this with our field and I get ORA-01843 - Invalid Month. It seems data related so I'll see if I can meet with our DBA. Thanks for your help!

Posting Permissions

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