Home » SQL & PL/SQL » SQL & PL/SQL » problem with evaluation of function
problem with evaluation of function [message #225657] Tue, 20 March 2007 14:53 Go to next message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
Hi,



I have the following block of PL/SQL CODE

IF NOT ISDATE(VTIME_DATE,'YYYY-MM-DD HH:MI:SS:FF3')
THEN
VTIME_DATE_DT:= TO_DATE(CURRENT_TIMESTAMP,'YYYY-MM-DD HH:MI:SS:FF3');
END IF;


where the value in the VTIME_DATE field is :
2007-01-20 12:13:15:001

Yet, it fails the above function call (i.e. the code executes the TO_DATE function). Given the data, I would expect it to say that it is a valid date, and not perform the TO_DATE statement.

Any thoughts?



The isdate function is defined below

create or replace function isdate
( p_string in varchar2,
p_fmt in varchar2 := null)
return boolean
as
l_date date;
begin
l_date :=
to_date(p_string,p_fmt);
return TRUE;
exception
when others then
return FALSE;
end;


Thanks a lot
Re: problem with evaluation of function [message #225719 is a reply to message #225657] Wed, 21 March 2007 02:33 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
DATEs don't work with fractional parts of seconds, that's why Oracle has the TIMESTAMP data type. Loose the FF3 part in the TO_DATE calls. Read the SQL reference for the valid date formats.

What are you trying to do?

MHE

Re: problem with evaluation of function [message #225720 is a reply to message #225657] Wed, 21 March 2007 02:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle DATEs do not contain fractions of seconds; only TIMESTAMPs do. You will need to change your ISDATE function to use TO_TIMESTAMP instead of TO_DATE, or create a different function (say, ISTIMESTAMP).

Also, you should probably pass in a format of 'YYYY-MM-DD HH24:MI:SS:FF3' if hours of 13-24 are valid.


Ross Leishman
Re: problem with evaluation of function [message #225730 is a reply to message #225657] Wed, 21 March 2007 02:51 Go to previous messageGo to next message
jheronimus
Messages: 11
Registered: March 2007
Location: The Netherlands, Groninge...
Junior Member
CURRENT_TIMESTAMP is already of the date type.
Thus in the next line, you are trying to convert a date to a date.

VTIME_DATE_DT:= TO_DATE(CURRENT_TIMESTAMP,'YYYY-MM-DD HH:MI:SS:FF3');

So you can change this line in:

VTIME_DATE_DT:= CURRENT_TIMESTAMP;

If you want to ingnore the fractal seconds part, just rewrite your function as this:

create or replace function isdate
( p_string in varchar2,
p_fmt in varchar2 := null)
return boolean
as
l_date date;
begin
l_date := to_date( substr(p_string, 1, instr(p_string, '.') -1) ,p_fmt );
return TRUE;
exception
when others then
return FALSE;
end;

And leave the factal seconds out of the format, like this:

IF NOT ISDATE(VTIME_DATE,'YYYY-MM-DD HH:MI:SS')
THEN

In practice this should work, I think.

Jeroen de Jong




Re: problem with evaluation of function [message #225736 is a reply to message #225730] Wed, 21 March 2007 03:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
jheronimus wrote on Wed, 21 March 2007 08:51
CURRENT_TIMESTAMP is already of the date type.


I think you'll find that not true, Jeroen:
according to the SQL Reference
CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.


Just to be clear Wink

MHE
Re: problem with evaluation of function [message #225800 is a reply to message #225657] Wed, 21 March 2007 09:40 Go to previous message
bobby_smitty99
Messages: 12
Registered: February 2007
Junior Member
Thanks for all of your suggestions.

I got my code working.

bob
Previous Topic: DBTIMEZONE questions
Next Topic: PL/SQL question
Goto Forum:
  


Current Time: Wed Dec 07 22:06:54 CST 2016

Total time taken to generate the page: 0.11725 seconds