SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

datepart equivalent in oracle for date function

NeilCSEMay 13 2011 — edited May 14 2011

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;

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;

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.

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.

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.

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.

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:

NeilCSE

what could be the better replacement

Frank Kulash

Hi,

NeilCSE wrote:
what could be the better replacement

What's wrong with ADD_MONTHS?

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

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

1 - 10

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 11 2011

Added on May 13 2011

10 comments

61,601 views

SQL & PL/SQL

datepart equivalent in oracle for date function

Comments

Post Details