1 2 Previous Next 28 Replies Latest reply on Aug 7, 2012 2:56 PM by BluShadow Go to original post
      • 15. Re: DATE QUERY (Need to find the financial dates)
        BluShadow
        Solomon Yakobson wrote:
        BluShadow wrote:

        The answer is some basic date arithmetic....
        Yes, but a bit more complex than you posted. Try your solution on any date between January 1 and March 31:
        Depends what the OP wants exactly as it wasn't stated.

        You're probably right though and he does want the financial year in which the date itself falls, but he didn't make that clear. ;)
        • 16. Re: DATE QUERY (Need to find the financial dates)
          BluShadow
          How can that be a correct answer?

          a) it's an exact copy of what the OP has just posted except for the alias of the output column
          b) it does string manipulation to compare against numbers.

          If anyone provided a correct solution it was Solomon who expanded my answer to provide the financial year that the date falls in and correctly used oracle date functionality.
          • 17. Re: DATE QUERY (Need to find the financial dates)
            BluShadow
            Muthukumar Seshadri wrote:
            Hey blu,

            Both your query not running!

            Shooting a error saying


            " ORA-01847: day of month must be between 1 and last day of month "

            Anyways tanx fr trying!!
            That is an issue with your supplied date, not with the solutions myself or Solomon provided.
            • 18. Re: DATE QUERY (Need to find the financial dates)
              Solomon Yakobson
              You are replying to me but addressing Blue, so I am not sure who's query is not running. If you are referring to my query, keep in mind it uses date literals and date literals have format yyyy-mm-dd. So if, when prompted, you enter date in different format you will either get wrong results or an error. For example, if you enter today's date as 2012-07-08 it will be interpreted as july 8, 2012 and if you enter august 25, 2012 as 2012-25-08 yoy'll get an error. In any case, post SQL*Plus snippet showing query execution along with your input and errors.

              SY.
              • 19. Re: DATE QUERY (Need to find the financial dates)
                Solomon Yakobson
                BluShadow wrote:
                Depends what the OP wants exactly as it wasn't stated.
                I thought is was clearly stated: "I want to get the first and last financial dates of any year when i enter some date".

                SY.
                • 20. Re: DATE QUERY (Need to find the financial dates)
                  952667
                  Fine Blu..

                  Solomon may be correct in your point of view!

                  But as for as mine concerned, roopan posted my requirement than solomon provided and he posted it later to me!! So i given him!..

                  Do want me to give solomon the correct ans? or may be to addup the points???? ;-)
                  • 21. Re: DATE QUERY (Need to find the financial dates)
                    952667
                    Ya w8 w8!!


                    Am looking into the query!! ill respond you in fewer minutes!! Sorry dude!
                    • 22. Re: DATE QUERY (Need to find the financial dates)
                      954023
                      @ Blu and SY,

                      BluShadow wrote:
                      Depends what the OP wants exactly as it wasn't stated.

                      I thought is was clearly stated: "I want to get the first and last financial dates of any year when i enter some date".

                      SY.

                      TRY THIS QUERY



                      SELECT (CASE
                      WHEN TO_CHAR (TO_DATE (:DATE1, 'dd-mm-rrrr'), 'mm') < 04
                      THEN ( '01-April'||'-'||(TO_CHAR (TO_DATE (:DATE1, 'dd-mm-rrrr'), 'yyyy') - 1)
                      || ' TO '
                      ||'31-Mar'||'-'||(TO_CHAR (TO_DATE (:DATE1, 'dd-mm-rrrr'), 'yyyy'))
                      )
                      WHEN TO_CHAR (TO_DATE (:DATE1, 'dd-mm-rrrr'), 'mm') > 03
                      THEN ( '01-April'||'-'|| (TO_CHAR (TO_DATE (:DATE1, 'dd-mm-rrrr'), 'yyyy'))
                      || ' TO '
                      || '31-March'||'-'||(TO_CHAR (TO_DATE (:DATE1, 'dd-mm-rrrr'), 'rrrr') + 1)
                      )
                      END
                      ) FINANCIAL_YEAR
                      FROM DUAL
                      • 23. Re: DATE QUERY (Need to find the financial dates)
                        952667
                        You also did all -1 +1 to get the value,but it ends in error!!!!

                        Its all in the game dude!! stay cool.. Keep smiling..

                        And tanx again fr your statement of explanation



                        SQL> with t as (select sysdate as dt from dual)
                        2 --
                        3 --
                        4 --
                        5 select add_months(trunc(dt,'YYYY'),3) as start_dt
                        6 ,add_months(trunc(dt,'YYYY'),15)-1 as end_dt
                        7 from t
                        8 /

                        START_DT END_DT
                        -------------------- --------------------
                        01-APR-2012 00:00:00 31-MAR-2013 00:00:00
                        • 24. Re: DATE QUERY (Need to find the financial dates)
                          Please do not use the rude word 'dude' all the time to people who help you out on your basic question for which you did nothing to resolve it.
                          Also you never specified any formal criteria, so you now can not claim it ends 'in error'. Obviously what BluShadow posted is correct and elegant and efficient.

                          -------------
                          Sybrand Bakker
                          Senior Oracle DBA
                          • 25. Re: DATE QUERY (Need to find the financial dates)
                            Solomon Yakobson
                            ROOPAN wrote:

                            TRY THIS QUERY
                            Your query is correct but very inefficient. If you want just year numbers all you need is:
                            SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:DATE1,'dd-mm-rrrr'),-3),'yyyy" - "') ||
                                   TO_CHAR(ADD_MONTHS(TO_DATE(:DATE1,'dd-mm-rrrr'),9),'yyyy') FINANCIAL_YEAR
                            FROM DUAL
                            /
                            For example:
                            SQL> variable date1 varchar2(20)
                            SQL> exec :date1 := '07-08-2012';
                            
                            PL/SQL procedure successfully completed.
                            
                            SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:DATE1,'dd-mm-rrrr'),-3),'yyyy" - "') ||
                              2         TO_CHAR(ADD_MONTHS(TO_DATE(:DATE1,'dd-mm-rrrr'),9),'yyyy') FINANCIAL_YEAR
                              3  FROM DUAL
                              4  /
                            
                            FINANCIAL_Y
                            -----------
                            2012 - 2013
                            
                            SQL> exec :date1 := '01-04-2012';
                            
                            PL/SQL procedure successfully completed.
                            
                            SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:DATE1,'dd-mm-rrrr'),-3),'yyyy" - "') ||
                              2         TO_CHAR(ADD_MONTHS(TO_DATE(:DATE1,'dd-mm-rrrr'),9),'yyyy') FINANCIAL_YEAR
                              3  FROM DUAL
                              4  /
                            
                            FINANCIAL_Y
                            -----------
                            2012 - 2013
                            
                            SQL> exec :date1 := '31-03-2012';
                            
                            PL/SQL procedure successfully completed.
                            
                            SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE(:DATE1,'dd-mm-rrrr'),-3),'yyyy" - "') ||
                              2         TO_CHAR(ADD_MONTHS(TO_DATE(:DATE1,'dd-mm-rrrr'),9),'yyyy') FINANCIAL_YEAR
                              3  FROM DUAL
                              4  /
                            
                            FINANCIAL_Y
                            -----------
                            2011 - 2012
                            
                            SQL> 
                            SY.
                            1 person found this helpful
                            • 26. Re: DATE QUERY (Need to find the financial dates)
                              952667
                              Okay sorry for blu as Sybrand Bakker Senior Oracle DBA said!!!

                              Okay dude... It's my habitual way of speaking!! Am normally responding but it seems rude or harsh with you ppls!!! Tanx anyways fr advice
                              • 27. Re: DATE QUERY (Need to find the financial dates)
                                954023
                                @Solomon

                                Super sir. Yours query is simple and good .
                                Even mine too also right.I thought that we want to specify the particular year in some particular format .
                                Its ok surely your way of thinking will be more helpfull.
                                keep in touch.
                                • 28. Re: DATE QUERY (Need to find the financial dates)
                                  BluShadow
                                  ROOPAN wrote:
                                  @Solomon

                                  Super sir. Yours query is simple and good .
                                  Even mine too also right.
                                  No, there's nothing right about doing this...
                                  TO_CHAR (TO_DATE (:DATE1, 'dd-mm-rrrr'), 'mm') < 04
                                  ^                                            ^ ^  ^
                                  |                                            | |  |
                                  \--------- String --------------------------/  |  |
                                                                                 |  |
                                                             Numeric Comparison -/  |
                                                                                    |
                                                                   Numeric Value ---/
                                  It shows a lack of understanding of the underlying datatypes, and relies on implicit datatype conversion.
                                  It may work in this case, but this sort of slack programming is what introduces errors in code.

                                  Oracle provides excellent functionality for handling the DATE datatype arithmetically and for comparisons, without any need to convert things to strings or rely on implicit conversions.

                                  That's why the answer you posted should not be considered correct.
                                  1 2 Previous Next