| Help in Converting Minutes of Difference Between Dates to Decimal [message #408494] |
Tue, 16 June 2009 07:32  |
Hemanth123
Messages: 56
Registered: April 2009
|
Member |
|
|
Hi,
Please help me in converting the below SQL Server Code into Oracle.
I have a query in SQL Server as below :
SELECT bl.CHARTERER AS Charterer
, bl.SHIP_NAME AS VesselName
, bl.COMMODITY AS Commodity
, bl.LAYCAN_FROM AS LayDaysFrom
, bl.LAYCAN_TO AS LayDaysTo
, CASE WHEN bl.LAYCAN_FROM IS NOT NULL AND bl.LAYCAN_TO IS NOT NULL THEN CONVERT(DECIMAL(15,2),DATEDIFF(mi,bl.LAYCAN_FROM,bl.LAYCAN_TO)/60/24) ELSE 0.00 END AS LayTime,
....and rest of the select statement
where LAYCAN_FROM and LAYCAN_TO are start and end dates
The value of LayTime will be something like 2.00,3.00..etc..
But in Oracle when I am trying to do the same using the below query..
TO_NUMBER(TO_CHAR(((LAYCAN_TO - LAYCAN_FROM) * (24*60))/60/24,'999999999999999.99'),'999999999999999.99')...
I am getting the value as 3,2..but not decimals...
Please help me converting the value as the the conversion should be into NUMBER value but not CHAR
Thanks,
[Updated on: Tue, 16 June 2009 07:34] Report message to a moderator |
|
|
|
| Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408498 is a reply to message #408494] |
Tue, 16 June 2009 07:43  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
When you substract two dates in oracle you get the difference in DAYS.
To get the minutes you would have to multiply that by 60 * 24.
No conversions are necessary.
It basically would be
select (bl.LAYCAN_TO - bl.LAYCAN_FROM) * 60 * 24 ....
And please read the forum guide on how to format your posts.
|
|
|
|
|
|
|
|
| Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408503 is a reply to message #408494] |
Tue, 16 June 2009 07:58 |
Hemanth123
Messages: 56
Registered: April 2009
|
Member |
|
|
Hi Thomas,
I have done the same thing(you can see in my post)..
But when I am dividing the obtained minutes with 60/24(as you can see in my post and the user needs the o/p in that specific format) I was able to get to the value as 2.00 using TO_CHAR function..but when I am converting it to NUMBER then the o/p would be 2...
As the o/p should be in NUMBER...I am using TO_NUMBER function...
Thanks,
hemanth
|
|
|
|
|
|
| Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408510 is a reply to message #408494] |
Tue, 16 June 2009 08:25 |
Hemanth123
Messages: 56
Registered: April 2009
|
Member |
|
|
Hi Thomas,
As you can see in my post..this is what I have done..
TO_NUMBER(TO_CHAR(((LAYCAN_TO - LAYCAN_FROM) * (24*60))/60/24,'999999999999999.99'),'999999999999999.99')...
where 24*60 gives the difference in Minutes and I have divided it with 60 & 24 to get the value as like 2.00,3.00(need it in NUMBER data type) etc..also I need the o/p in NUMBER(15,2) format...hence used TO_CHAR function and TO_NUMBER function to convert it to NUMBER..
But the o/p Iam getting as 2,3..i.e.with out any decimals..
Any suggestions are welcome..
Thanks,
Hemanth.
|
|
|
|
| Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408513 is a reply to message #408510] |
Tue, 16 June 2009 08:30 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
To get things straight:
What do you NEED?
Do you need a NUMBER?
A number is 2 or 2.12 or 2.1 or .21
Or do you need a FORMATTED STRING, which is 2.00 or 2.12 or 2.10 or 0.21?
If you need a NUMBER, then just subtract the dates, and forget all the to_number, to_char stuff.
If you need the formatted string then just use ONE to_char on the final result.
But DEFINITELY get rid of all to_number calls.
|
|
|
|
|
|
| Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408515 is a reply to message #408494] |
Tue, 16 June 2009 08:41 |
Hemanth123
Messages: 56
Registered: April 2009
|
Member |
|
|
Hi Thomas,
Yeah..I need a NUMBER of value like NUMBER(15,2) i.e.only 2 values after decimal.
I need a NUMBER o/p like 2.12,3.00,..etc.
If I just simply substract the dates and multiply it with 60*24 and again divide with 60 & 24 I am getting the result as 2.99993555 i.e.the precise value should be only 2 values after decimal.
Hence I used the conversion functions..
Any suggestions?
Thanks,
|
|
|
|
|
|
| Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408519 is a reply to message #408515] |
Tue, 16 June 2009 09:12 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Hemanth123 wrote on Tue, 16 June 2009 15:41 | Hi Thomas,
Yeah..I need a NUMBER of value like NUMBER(15,2) i.e.only 2 values after decimal.
I need a NUMBER o/p like 2.12,3.00,..etc.
If I just simply substract the dates and multiply it with 60*24 and again divide with 60 & 24 I am getting the result as 2.99993555 i.e.the precise value should be only 2 values after decimal.
Hence I used the conversion functions..
Any suggestions?
Thanks,
|
Take that numeric result and round it using proper numeric function.
In Oracle, it is (not surprisingly) called ROUND and it is described in SQL Reference book - a part of Oracle documentation available e.g. online on http://tahiti.oracle.com/.
|
|
|
|
|
|
| Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408586 is a reply to message #408494] |
Wed, 17 June 2009 00:21 |
Hemanth123
Messages: 56
Registered: April 2009
|
Member |
|
|
Thanks for your suggestions...
I have tried using ROUND function but not able to get the desired result i.e.like 3.2,2.00(you can check either)..hence asked for your help and posted this topic..
When I query the below stmt I get the o/p as 3 but not 3.00
select round(((SYSDATE - (SYSDATE-3)) * 60 * 24)/60/24,2) from dual;
Please let me know any other suggestions...
Thanks,
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
