Home » SQL & PL/SQL » SQL & PL/SQL » Minute Differences (oracle 10g)
Minute Differences [message #311641] Sat, 05 April 2008 06:24 Go to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
Select ((To_Date('04-04-2008 06:23:45', 'DD-MM-YYYY HH24.MI.SS') - To_Date('03-04-2008 23:23:45', 'DD-MM-YYYY HH24.MI.SS') ) ) * (1440) TotMins
From Dual

this above SQL gives the Minute Difference from one todatetime to fromdatetime...

when i pass a parameter to this in my application
i am getting the following error...



ORA-01840: input value not long enough for date format ORA-06512

Regards
Muthu

Re: Minute Differences [message #311643 is a reply to message #311641] Sat, 05 April 2008 06:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Show us the ACTUAL code you used and the ACTUAL value passed in.

Ross Leishman
Re: Minute Differences [message #311645 is a reply to message #311643] Sat, 05 April 2008 07:08 Go to previous messageGo to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
Select ((To_Date( Nvl( :TDateTime , SysDate ) , 'DD-MM-YYYY HH24.MI.SS') - To_Date( Nvl ( :FDateTime , SysDate ) , 'DD-MM-YYYY HH24.MI.SS') ) ) * (1440) TotMins
From Dual



Parameters -
:TDateTime
:FDateTime
Re: Minute Differences [message #311648 is a reply to message #311645] Sat, 05 April 2008 07:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why do you have the NVL? Are you sometimes passing empty bind variables?

You should not have different data types in the two arguments of NVL(). Make them either both VARCHAR2 or both DATE.

eg.
TO_DATE(NVL(:TDateTime, TO_CHAR(sysdate, 'DD-MM-YYYY HH24.MI.SS'))
       ,'DD-MM-YYYY HH24.MI.SS')


Ross Leishman
Re: Minute Differences [message #311651 is a reply to message #311648] Sat, 05 April 2008 07:21 Go to previous messageGo to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
i HAVE CHANGED AS U SAID...

BUT STILL THE SAME ERROR SHOWS...

ORA-01840: input value not long enough for date format
Re: Minute Differences [message #311653 is a reply to message #311645] Sat, 05 April 2008 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
mudalimuthu wrote on Sat, 05 April 2008 05:08
Parameters -
:TDateTime
:FDateTime


What are data types & actual value for these variables?
Simply put, the input data is what is causing the error.
Re: Minute Differences [message #312174 is a reply to message #311653] Tue, 08 April 2008 02:37 Go to previous messageGo to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member

:TDateFrom
:FDateFrom

are varchar2 datatypes which contains data like '08-04-2006 08.15.20'
Re: Minute Differences [message #312181 is a reply to message #311641] Tue, 08 April 2008 02:47 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Still no clue what kind of "application" you're talking about: is it a forms application? Java? Pro*C?
Re: Minute Differences [message #312185 is a reply to message #312181] Tue, 08 April 2008 02:50 Go to previous messageGo to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
its an external application which is written in Delphi..
Re: Minute Differences [message #312189 is a reply to message #311641] Tue, 08 April 2008 02:53 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
As far as I know Delphi doesn't have a datatype called "VARCHAR".
It would have something similar called String or so.

Bottom-line: it's a Delphi-related problem and not really an Oracle one Wink

Delve into the Delphi-documentation, and try to figure out if there is any restriction on the datatype you're using

Re: Minute Differences [message #312190 is a reply to message #312189] Tue, 08 April 2008 02:56 Go to previous message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
thank you
Previous Topic: Execution of Clob through dynamic sql
Next Topic: to display top ten and bottom ten student as per their grade
Goto Forum:
  


Current Time: Tue Dec 06 14:12:22 CST 2016

Total time taken to generate the page: 0.05669 seconds