Forum Stats

  • 3,783,752 Users
  • 2,254,826 Discussions
  • 7,880,537 Comments

Discussions

datepart equivalent in oracle for date function

NeilCSE
NeilCSE Member Posts: 219
edited May 14, 2011 2:50AM in SQL & PL/SQL
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;

Answers

  • Ganesh Srivatsav
    Ganesh Srivatsav Member Posts: 2,284
    edited May 13, 2011 2:01PM
    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.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,424 Red Diamond
    edited May 13, 2011 2:06PM
    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.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    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.
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,424 Red Diamond
    edited May 13, 2011 2:36PM
    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.
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,026 Red Diamond
    edited May 13, 2011 2:34PM
    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.
  • NeilCSE
    NeilCSE Member Posts: 219
    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:
  • NeilCSE
    NeilCSE Member Posts: 219
    what could be the better replacement
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,424 Red Diamond
    Hi,
    NeilCSE wrote:
    what could be the better replacement
    What's wrong with ADD_MONTHS?
  • NeilCSE
    NeilCSE Member Posts: 219
    edited May 14, 2011 1:05AM
    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
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown
    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
This discussion has been closed.