10 Replies Latest reply: May 14, 2011 1:50 AM by William Robertson RSS

    datepart equivalent in oracle for date function

    NeilCSE
      I was trying to convert the sqlserver query to oracle and found a function which is not available in ora, could you plz help me in converting this

      select top 5 ssm.maturity,datepart(mm, dateadd(mm, -3, ssm.maturity)) from sec_ind_ss ssm;
      
      2014-10-23 00:00:00.0       7
      9999-12-31 00:00:00.0       9
      2021-01-02 00:00:00.0       10
      2022-07-31 00:00:00.0       4
      2019-07-31 00:00:00.0       4
      
      
      I tried this but no result
      
      SELECT ssm.maturity ,CAST(EXTRACT(MONTH FROM ssm.maturity - NUMTOYMINTERVAL(3,'month')) AS VARCHAR2(30))
      from sec_ind_ss ssm WHERE ROWNUM<=5;
        • 1. Re: datepart equivalent in oracle for date function
          Ganesh Srivatsav
          You can simply do this,

          extract function in oracle by default returns a number.
          SELECT ssm.maturity, EXTRACT (MONTH FROM ssm.maturity)-3
            FROM sec_ind_ss ssm
           WHERE ROWNUM <= 5;
          If you need to explicitly convert the result to varchar2(30) then,
          SELECT ssm.maturity, CAST (EXTRACT (MONTH FROM ssm.maturity) - 3 AS VARCHAR2 (30))
            FROM sec_ind_ss ssm
           WHERE ROWNUM <= 5;
          G.
          • 2. Re: datepart equivalent in oracle for date function
            Frank Kulash
            Hi,

            Try this:
            TO_CHAR ( ADD_MONTHS ( ssm.maturity
                             , -3
                           )
                 , 'fmMM'
                 )
            There are a lot of differences in DATE functions between Oracle and SQL Server.
            ADD_MONTHS in Oracle corresponds to DATEADD in SQL Server for months and years. For days, hours, minutes and seconds, use Date Artithmetic .
            EXTRACT and CAST, like you tried, would work, but TO_CHAR does the same thing in one step.
            • 3. Re: datepart equivalent in oracle for date function
              MichaelS
              I tried this but no result
              In principle your statement looks correct (though it can be made simpler), but I don't get why you get no result:
              SQL> with sec_ind_ss as
              ( 
               select date '2014-10-23' maturity from dual
              )
              --
              --
              select cast (extract (month from maturity - numtoyminterval (3, 'month')) as varchar2 (30)) maturity from sec_ind_ss
              /
              MATURITY                                     
              ---------------------------------------------
              7                                            
              1 row selected.
              • 4. Re: datepart equivalent in oracle for date function
                Frank Kulash
                Hi,
                MichaelS wrote:
                I tried this but no result
                In principle your statement looks correct (though it can be made simpler), but I don't get why you get no result:
                It's because of end-of-month effects.
                SELECT      DATE '2011-05-29' - NUMTOYMINTERVAL (3, 'MONTH')     AS d
                FROM      dual;
                raises "ORA-01839: date not valid for month specified' because February 29, 2011 is not a valid date.
                • 5. Re: datepart equivalent in oracle for date function
                  Solomon Yakobson
                  MichaelS wrote:

                  but I don't get why you get no result:
                  You should be careful with intervals:
                  with sec_ind_ss as
                  (
                   select date '2014-12-31' maturity from dual
                  )
                  --
                  --
                  select cast (extract (month from maturity - numtoyminterval (3, 'month')) as varchar2 (30)) maturity from sec_ind_ss
                  /
                                                            *
                  ERROR at line 7:
                  ORA-01839: date not valid for month specified
                  
                  
                  SQL> 
                  SY.
                  • 6. Re: datepart equivalent in oracle for date function
                    NeilCSE
                    yes I am getting the same error

                    ORA-01839: date not valid for month specified
                    01839. 00000 - "date not valid for month specified"
                    *Cause:   
                    *Action:                                                                                                                                                                                                                                                                                                                           
                    • 7. Re: datepart equivalent in oracle for date function
                      NeilCSE
                      what could be the better replacement
                      • 8. Re: datepart equivalent in oracle for date function
                        Frank Kulash
                        Hi,
                        NeilCSE wrote:
                        what could be the better replacement
                        What's wrong with ADD_MONTHS?
                        • 9. Re: datepart equivalent in oracle for date function
                          NeilCSE
                          I tried that, once but for jan month it was not giving correct results

                          jan -3 = should give 10 and It was giving -2


                          for jan it should be as below
                          2021-01-02 00:00:00.0     10
                          • 10. Re: datepart equivalent in oracle for date function
                            William Robertson
                            Really? Here's what I get:
                            alter session set nls_date_format = 'RRRR-MM-DD';
                            col date_minus3 format a11
                            with sec_ind_ss as
                             ( select date '2014-01-31' maturity from dual union all
                               select date '2014-10-23' from dual union all
                               select date '9999-12-31' from dual union all
                               select date '2021-01-02' from dual union all
                               select date '2022-07-31' from dual union all
                               select date '2019-07-31' from dual )
                            --
                            select maturity
                                 , add_months(maturity,-3) as date_minus3
                                 , to_number(to_char(add_months(maturity,-3),'fmMM')) as month_minus3
                            from   sec_ind_ss;
                            MATURITY   DATE_MINUS3 MONTH_MINUS3
                            ---------- ----------- ------------
                            2014-01-31 2013-10-31            10
                            2014-10-23 2014-07-23             7
                            9999-12-31 9999-09-30             9
                            2021-01-02 2020-10-02            10
                            2022-07-31 2022-04-30             4
                            2019-07-31 2019-04-30             4