OraFAQ Forum: SQL & PL/SQL » Minute Differences

Home » SQL & PL/SQL » SQL & PL/SQL » Minute Differences (oracle 10g)

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

Minute Differences [message #311641]

Sat, 05 April 2008 06:24

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

Report message to a moderator

Re: Minute Differences [message #311643 is a reply to message #311641]

Sat, 05 April 2008 06:59

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Show us the ACTUAL code you used and the ACTUAL value passed in.

Ross Leishman

Report message to a moderator

Re: Minute Differences [message #311645 is a reply to message #311643]

Sat, 05 April 2008 07:08

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

Report message to a moderator

Re: Minute Differences [message #311648 is a reply to message #311645]

Sat, 05 April 2008 07:15

rleishman
Messages: 3728
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

Report message to a moderator

Re: Minute Differences [message #311651 is a reply to message #311648]

Sat, 05 April 2008 07:21

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

Report message to a moderator

Re: Minute Differences [message #311653 is a reply to message #311645]

Sat, 05 April 2008 08:38

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Minute Differences [message #312174 is a reply to message #311653]

Tue, 08 April 2008 02:37

mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore

Member

:TDateFrom
:FDateFrom

are varchar2 datatypes which contains data like '08-04-2006 08.15.20'

Report message to a moderator

Re: Minute Differences [message #312181 is a reply to message #311641]

Tue, 08 April 2008 02:47

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?

Report message to a moderator

Re: Minute Differences [message #312185 is a reply to message #312181]

Tue, 08 April 2008 02:50

mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore

Member

its an external application which is written in Delphi..

Report message to a moderator

Re: Minute Differences [message #312189 is a reply to message #311641]

Tue, 08 April 2008 02:53

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

Report message to a moderator

Re: Minute Differences [message #312190 is a reply to message #312189]

Tue, 08 April 2008 02:56

mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore

Member

thank you

Report message to a moderator

Previous Topic:	Execution of Clob through dynamic sql
Next Topic:	to display top ten and bottom ten student as per their grade

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 18 01:43:55 CST 2025