Home » SQL & PL/SQL » SQL & PL/SQL » Minus Minutes (11g)
Minus Minutes [message #655279] Fri, 26 August 2016 01:02 Go to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Dear All,

I want to minus minutes from minutes. I run this query which runs sucessfully

Select To_Char(SYSDATE, 'DD/MM/YYYY HH24MI')
from dual

26/08/2016 1055

but when i minus to minutes it gives me error

Select To_Number(To_Char(SYSDATE, 'DD/MM/YYYY HH24MI'))
- To_Number(To_Char(SYSDATE + 10/1440,'DD/MM/YYYY HH24MI'))
from dual;

ERROR at line 1:
ORA-01722: invalid number


I want to minus current minutes with 10 minutes
Re: Minus Minutes [message #655280 is a reply to message #655279] Fri, 26 August 2016 01:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here you go:
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate, sysdate - 10/(24 * 60) ten_minutes_earlier from dual;

SYSDATE             TEN_MINUTES_EARLIER
------------------- -------------------
26.08.2016 08:06:28 26.08.2016 07:56:28

SQL>
Re: Minus Minutes [message #655281 is a reply to message #655280] Fri, 26 August 2016 01:09 Go to previous messageGo to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
It works Thanks a lot
Re: Minus Minutes [message #655282 is a reply to message #655281] Fri, 26 August 2016 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you understand why your query returns an error?

Re: Minus Minutes [message #655283 is a reply to message #655282] Fri, 26 August 2016 01:36 Go to previous messageGo to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Because it returns in number
Re: Minus Minutes [message #655284 is a reply to message #655283] Fri, 26 August 2016 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What "it" is?

Note: this is not the reason of the error.

Re: Minus Minutes [message #655289 is a reply to message #655284] Fri, 26 August 2016 02:13 Go to previous messageGo to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Then i didnt know the reason
Re: Minus Minutes [message #655291 is a reply to message #655289] Fri, 26 August 2016 02:18 Go to previous messageGo to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Can you please tell me the reason so i can know what is the reason
Re: Minus Minutes [message #655292 is a reply to message #655289] Fri, 26 August 2016 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select To_Char(SYSDATE, 'DD/MM/YYYY HH24MI') from dual;
TO_CHAR(SYSDATE
---------------
26/08/2016 0917

Can you apply TO_NUMBER on this?

With spoonfeeding you learn nothing.
Re: Minus Minutes [message #655295 is a reply to message #655292] Fri, 26 August 2016 02:43 Go to previous message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
yeah you are right and thanks for sharing information
Previous Topic: Script Help
Next Topic: How to identify the partition type
Goto Forum:
  


Current Time: Thu Mar 28 06:32:02 CDT 2024