14 Replies Latest reply: May 27, 2013 3:55 PM by 1002942 RSS

    please where am i  wrong

    1002942
      Create a function get_return_date. It is intended to be stored in a variable that is printed on a lease.

      Example: One person goes into a hire firm and rents an item in, say 7 days. If it is the 4th of October at 23:59, the function returns: date + hour + minutes,


      SELECT get_return_date(7)
      FROM dual;


      GET_RETURN_DATE(7)
      ----------------------------
      2004-10-11:23

      ...................................................................................................
      code

      CREATE OR REPLACE FUNCTION GET_return_date(P_RENT_DATE DATE)
      RETURN DATE
      BEGIN
      RETURN SYSDATE + 7;
      END;


      SELECT get_date_function(7)
      FROM dual;
        • 1. Re: please where am i  wrong
          sb92075
          999939 wrote:
          Create a function get_return_date. It is intended to be stored in a variable that is printed on a lease.

          Example: One person goes into a hire firm and rents an item in, say 7 days. If it is the 4th of October at 23:59, the function returns: date + hour + minutes,


          SELECT get_return_date(7)
          FROM dual;


          GET_RETURN_DATE(7)
          ----------------------------
          2004-10-11:23

          ...................................................................................................
          code

          CREATE OR REPLACE FUNCTION GET_return_date(P_RENT_DATE DATE)
          RETURN DATE
          BEGIN
          RETURN SYSDATE + 7;
          END;


          SELECT get_date_function(7)
          FROM dual;
          what exactly should correct/desired answer look like?


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: please where am i  wrong
            1002942
            desired/correct answer is


            GET_RETURN_DATE(7)
            ----------------------------
            2004-10-11:23
            • 3. Re: please where am i  wrong
              ranit B
              Please explain clearly what is your expected output.

              You are creating function with an input parameter of DATE:
              CREATE OR REPLACE FUNCTION GET_return_date(P_RENT_DATE DATE)
              RETURN DATE
              BEGIN
              RETURN SYSDATE + 7;
              END;
              But while calling why a NUMBER is passed as IN parameter?
              SELECT get_return_date(7)
              FROM dual;
              • 4. Re: please where am i  wrong
                1002942
                please correct me if u can

                thank u
                • 5. Re: please where am i  wrong
                  ranit B
                  999939 wrote:
                  desired/correct answer is


                  GET_RETURN_DATE(7)
                  ----------------------------
                  2004-10-11:23
                  It means your output is in format: yyyy-mm-dd:hh24
                  Is it correct?

                  And what about the 'Minute' part?
                  • 6. Re: please where am i  wrong
                    ranit B
                    Please try this...
                    ranit@XE11GR2>> CREATE OR REPLACE FUNCTION GET_return_date(P_RENT_DATE DATE)
                      2  RETURN DATE
                      3  IS
                      4  BEGIN
                      5  RETURN P_RENT_DATE + 7;
                      6  END;
                      7  /
                    
                    Function created.
                    
                    ranit@XE11GR2>> select
                      2     TO_CHAR(ret_dt, 'yyyy-mm-dd:hh24') return_dt
                      3  from (
                      4     select get_return_date(TO_DATE('04-10-2013 23:59','dd-mm-yyyy hh24:mi')) ret_dt
                      5     from dual
                      6     );
                    
                    RETURN_DT
                    -------------
                    2013-10-11:23
                    Here:
                    1- The function takes a DATE as IN parameter and also returns a DATE.
                    2- While invoking the function from a query, we need to format the DATE type as per requirement using proper format model in TO_CHAR.

                    Hope this helps.

                    Edited by: ranit B on May 28, 2013 12:44 AM
                    -- Modified the Query...
                    • 7. Re: please where am i  wrong
                      1002942
                      i was thinking the parameter on the function should be 7
                      • 8. Re: please where am i  wrong
                        Greg Spall
                        999939 wrote:
                        i was thinking the parameter on the function should be 7
                        yeah, that's fine ... that's ok, but what the heck does this mean??

                        GET_RETURN_DATE(7)
                        2004-10-11:23

                        ???

                        This format doesn't make any sense to me (and is probably confusing others as well).

                        I might expect:

                        yyyy-mm-dd hh:mm:ss

                        such as: 2013-10-04 23:59:59

                        But trying to figure out what you printed? No idea. Can't help until we understand that better.
                        • 9. Re: please where am i  wrong
                          ranit B
                          999939 wrote:
                          i was thinking the parameter on the function should be 7
                          What will you do just by passing '7'? You also need to have the DATE parameter, right?

                          Please explain your requirement completely... Else it's no point replying you all time.

                          Do you understand the basics of Parameters?
                          ranit@XE11GR2>> CREATE OR REPLACE FUNCTION get_return_date_x(P_RENT_DATE DATE, days number)
                            2  RETURN DATE
                            3  IS
                            4  BEGIN
                            5     RETURN P_RENT_DATE + days;
                            6  END;
                            7  /
                          
                          Function created.
                          
                          ranit@XE11GR2>> select
                            2         TO_CHAR(ret_dt, 'yyyy-mm-dd:hh24') return_dt
                            3      from (
                            4         select get_return_date_x(TO_DATE('04-10-2013 23:59','dd-mm-yyyy hh24:mi'),7) ret_dt
                            5         from dual
                            6         );
                          
                          RETURN_DT
                          -------------
                          2013-10-11:23
                          • 10. Re: please where am i  wrong
                            EdStevens
                            999939 wrote:
                            i was thinking the parameter on the function should be 7
                            And why were you thinking that?
                            you defined the date with an input paramter of a date
                            CREATE OR REPLACE FUNCTION GET_return_date(P_RENT_DATE DATE)
                            Is 7 a date?

                            Then your function returns
                            RETURN SYSDATE + 7;
                            And never even referenced the input parameter at all.
                            • 11. Re: please where am i  wrong
                              Greg Spall
                              EdStevens wrote:
                              you defined the date with an input paramter of a date

                              And never even referenced the input parameter at all.
                              lol I didn't even notice that when I was reading, I just got stuck on the "what the heck does that odd output mean?" O_0
                              • 12. Re: please where am i  wrong
                                ranit B
                                Greg.Spall wrote:
                                EdStevens wrote:
                                you defined the date with an input paramter of a date

                                And never even referenced the input parameter at all.
                                lol I didn't even notice that when I was reading, I just got stuck on the "what the heck does that odd output mean?" O_0
                                I already informed this long back... Check my first post. ;)
                                • 13. Re: please where am i  wrong
                                  John Spencer
                                  I think you are looking for something like:
                                  SQL> create function get_return_date (p_rental_days in number) return varchar2 as
                                    2  begin
                                    3     return to_char(sysdate + p_rental_days, 'dd-mon-yyyy hh24:mi');
                                    4  end;
                                    5  /
                                  
                                  Function created.
                                  
                                  SQL> select get_return_date(7) from dual;
                                  03-jun-2013 16:45
                                  You can adjust the format mask in the to_char to suit.

                                  John
                                  • 14. Re: please where am i  wrong
                                    1002942
                                    thanks alot john that answered my question