5 Replies Latest reply: Jan 25, 2013 4:22 AM by BluShadow RSS

    Current qtr end date

    980493
      Hi experts,

      My below qry is working fine with sysdate but when m passing a date it's throwing an error to get the current qtr end date..
      select add_months(trunc('20-dec-2012','q'),3) - 1 from dual
      ERROR
      ERROR at line 1:
      ORA-00932: inconsistent datatypes: expected DATE got NUMBER
      even after adding the to_date function between trunc and date...
      
      Kindly help
      
      Thnx in advance...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        • 1. Re: Current qtr end date
          Karthick_Arp
          977490 wrote:
          Hi experts,

          My below qry is working fine with sysdate but when m passing a date it's throwing an error to get the current qtr end date..
          select add_months(trunc('20-dec-2012','q'),3) - 1 from dual
          ERROR
          ERROR at line 1:
          ORA-00932: inconsistent datatypes: expected DATE got NUMBER
          even after adding the to_date function between trunc and date...

          Kindly help

          Thnx in advance...
          SYSDATE is DATE and you are passing a STRING to the trunc function. Thats the reason SYSDATE dint give you error and the above query gives error. Change it as
          select add_months(trunc(to_date('20-dec-2012', 'dd-mon-yyyy'),'q'),3) - 1 from dual
          • 2. Re: Current qtr end date
            John Stegeman
            prove to us that it doesn't work, even with the proper to_date

            Here's my proof that it does:
            SQL> select add_months(trunc(to_date('20-DEC-2012', 'DD-MON-YYYY'), 'q'), 3)-1 f
            rom dual;                                                                       
                                                                                            
            ADD_MONTH                                                                       
            ---------                                                                       
            31-DEC-12                                                                       
            Version 11.2.0.3, Windows 64-bit
            • 3. Re: Current qtr end date
              980493
              Thnx

              Edited by: 977490 on Jan 25, 2013 2:16 AM
              • 4. Re: Current qtr end date
                John Stegeman
                In your reply that you just edited to say "thnx"

                No, you should not do it without to_date. You have a string, not a date. Oracle will pick up the default date format from NLS settings, which may not be appropriate for your string.

                ALWAYS use to_date when you need a date and you have a string
                • 5. Re: Current qtr end date
                  BluShadow
                  Can you achieve it without to_date... why?

                  the
                  TRUNC(<argument1>, 'Q')
                  call requires that argument1 is a DATE datatype.

                  In your original code you were passing a VARCHAR2 datatype, so Oracle was trying to implicitly convert it to a DATE datatype, but problems existed becuase the string you provided wasn't in the correct format for the NLS settings on your database.

                  You are always better to explicitly ensure that the arguments to functions are the correct datatype.

                  Take a string literal of a known format and convert it to date using TO_DATE with the correct format string...
                  SQL> select add_months(trunc(to_date('20-DEC-2012', 'DD-MON-YYYY'), 'q'), 3)-1 from dual;
                  
                  ADD_MONTHS(TRUNC(TO_
                  --------------------
                  31-DEC-2012 00:00:00
                  Take a string literal known to be using Oracle internal fixed format for date strings, and use the in built DATE function to indicate it's a DATE datatype
                  SQL> select add_months(trunc(date '2012-12-20', 'q'), 3)-1 from dual;
                  
                  ADD_MONTHS(TRUNC(DAT
                  --------------------
                  31-DEC-2012 00:00:00
                  Or, Provide something that is already known to be a DATE datatype...
                  SQL> select add_months(trunc(sysdate, 'q'), 3)-1 from dual;
                  
                  ADD_MONTHS(TRUNC(SYS
                  --------------------
                  31-MAR-2013 00:00:00