Home » SQL & PL/SQL » SQL & PL/SQL » Help in Converting Minutes of Difference Between Dates to Decimal
Help in Converting Minutes of Difference Between Dates to Decimal [message #408494] Tue, 16 June 2009 07:32 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #408499 is a reply to message #408494] Tue, 16 June 2009 07:44 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
The difference between 2 dates in Oracle is in days. Unless your dates are exactly the same millisecond, you are going to get a decimal value.
Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408501 is a reply to message #408494] Tue, 16 June 2009 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has already been asked to you:
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
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 Go to previous messageGo to next message
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 #408505 is a reply to message #408503] Tue, 16 June 2009 08:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I have done the same thing(you can see in my post)..



No, you haven't.

No conversions are necessary.

Forget the whole to_char / to_number crap. Just subtract the dates and you have the difference in days as a decimal number.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #408514 is a reply to message #408494] Tue, 16 June 2009 08:35 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I was going to say what ThomasG just said.
So I'll just say this:
What's the difference between
(value * (24*60))/60/24
and
value?

Answer: nothing

So why are you doing that unecessary math?
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 Go to previous messageGo to next message
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 #408516 is a reply to message #408515] Tue, 16 June 2009 08:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
I need a NUMBER o/p like 2.12,3.00,..etc.


That is not possible. If the data type is NUMBER, then you get 3 and NOT 3.00

Any formatting of that number would have to happen in the client application.

So make up your mind if you want a number of a formatted string.

[Updated on: Tue, 16 June 2009 08:44]

Report message to a moderator

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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #408547 is a reply to message #408494] Tue, 16 June 2009 12:25 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Use the round function. For two decimal points


select round((bl.LAYCAN_TO - bl.LAYCAN_FROM) * 60 * 24,2) ...
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 Go to previous messageGo to next message
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,
Re: Help in Converting Minutes of Difference Between Dates to Decimal [message #408629 is a reply to message #408586] Wed, 17 June 2009 02:23 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What "other" suggestions? Don't you actually read the answers?

If you want a NUMBER returned you can't get "3.00" returned.

If you want the formatted CHARACTER STRING 3.00 returned, then you have to use to_char on the result.
Previous Topic: Please help me
Next Topic: view modification
Goto Forum:
  


Current Time: Thu Dec 08 22:05:55 CST 2016

Total time taken to generate the page: 0.11630 seconds