OraFAQ Forum: SQL & PL/SQL » Convert days to months

Home » SQL & PL/SQL » SQL & PL/SQL » Convert days to months (Oracle 8i, Windows 2007)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Convert days to months [message #532312]

Mon, 21 November 2011 23:17

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Dear Experts,

I have the following in my select statement and I am getting the days like 1130, 50, 60 etc

round(MAX (TO_DATE ('31-OCT-2011 23:59:59','dd-mon-yyyy hh24:mi:ss') - a.tx_dtime),0) DAYS

Now I want this to convert these days into months like
1.10 this denotes (one month 10 days)
1.25 this denotes (one month 25 days)
2.05 this denotes (two months 5 days)

Can anyone help in this regard? I know I am using old Oracle but what to do my company is not spending to upgrade.

Report message to a moderator

Re: Convert days to months [message #532313 is a reply to message #532312]

Mon, 21 November 2011 23:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can anyone help in this regard?
How many days in your standard month?

Report message to a moderator

Re: Convert days to months [message #532314 is a reply to message #532313]

Mon, 21 November 2011 23:25

kumarvk
Messages: 214
Registered: July 2004

Senior Member

I take 30 days as standard

Report message to a moderator

Re: Convert days to months [message #532326 is a reply to message #532314]

Tue, 22 November 2011 00:57

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Why do you want to convert something useful to make maths to something useless?

This is basic arithmetic, use MOD and TRUNC.

Regards
Michel

Report message to a moderator

Re: Convert days to months [message #532341 is a reply to message #532326]

Tue, 22 November 2011 01:26

kumarvk
Messages: 214
Registered: July 2004

Senior Member

Dear Mr.Michel,

I am using this in my sql when then tx_dtime is 10-OCT-1999 but getting the result in negative if I use DD-MON-YYYY I am getting error message

(full) year must be between -4713 and +9999, and not be 0

how to rectify this problem?

trunc(months_between(last_day(sysdate),TO_DATE(DECODE (a.TYPE, 'SI', MAX(A.TX_DTIME),
DECODE (a.TYPE, 'AI', MAX(A.TX_DTIME))),'DD-MON-YY'))) mths

Report message to a moderator

Re: Convert days to months [message #532344 is a reply to message #532341]

Tue, 22 November 2011 01:29

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just use the TWO functions I gave you, no other one is needed (unless you modify you requirements about "month" definition).

If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: Convert days to months [message #532435 is a reply to message #532341]

Tue, 22 November 2011 08:40

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Quote:

trunc(months_between(last_day(sysdate),TO_DATE(DECODE (a.TYPE, 'SI', MAX(A.TX_DTIME),
DECODE (a.TYPE, 'AI', MAX(A.TX_DTIME))),'DD-MON-YY'))) mths

You are using TO_DATE on a DATE column. This is wrong.

[Updated on: Tue, 22 November 2011 08:41]

Report message to a moderator

Previous Topic:	how does '<' operator works for varchar2 datatype columns
Next Topic:	PL/SQL Package for Web Service (2 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 09 13:48:11 CDT 2025