How to test for a date value in a Firebird stored procedure?

Hello All!

I'm trying to write a Firebird (1.5) stored procedure that checks a VARCHAR column for an entry that is a date (e.g. 01/01/2006). If a date isn't found, my procedure then runs an update to insert a date into the column (regardless of what the current non-date value is).

Does anybody have any ideas how to check for a date value? The VARCHAR column will contain various text entries, some of which will be dates in the format shown above. I was hoping for some magic function like 'IsDate' - but I've run into brick wall after brick wall.

I just don't know enough about Firebird stored procedures, and I'm not sure if I'm trying to achieve something that can't be done in a stored procedure. It's been a good learning exercise trying to do this - but I think that I am going to perhaps have to write a small Delphi utility to achieve the job.

Why didn't I write the Delphi utility in the first place? Because I want to know more about stored procedures!

Many thanks in advance,

SKING

I would try to CAST it as a date and handle exceptions.

Hi Pabloj - thanks for the reply.

I tried something similar (using EXTRACT), and I have just tried using CAST as you suggested, but when I run the procedure it doesn't update any records, and it looks as though my exception handler is doing anything.

I thought 'when any do' should capture all exceptions. I assume that I'm using it wrong. Here's the test procedure...



CREATE PROCEDURE FIXDATASHORTDATES
AS
DECLARE VARIABLE PATNO INTEGER;
DECLARE VARIABLE ENC_DATE DATE;
DECLARE VARIABLE DATA_SHORT varchar(200);
DECLARE VARIABLE TEMP_DATE DATE;
begin
FOR
select patno, enc_date, data_short from enc_details
where data_rc = 'DateSeen'
and patno = 23
into atno, :enc_date, :data_short
do begin
temp_date = CAST (:data_short AS date);
when any do
begin
update enc_details set data_short = :enc_date
where patno = atno and enc_date = :enc_date;
end
end
end


When I try to debug the procedure (IBExpert) I get the following error:

Overflow occurred during data type conversion.
conversion error from string "29/04/2003"

I feel like I'm on the right track, but I'm dammed if I know what's going wrong!

Cheers,

SKING

Seems to work for me

Seriously? Damn! I wonder what's going on my end??!

Could this have anything to do with SQL Dialects, do you think? The Firebird database that I'm running against is Dialect 1. I'm clutching at straws, here, but I just can't see why the debugger in IBExpert doesn't take me into the 'when any do' block. Running the procedure against my test data yields no results.

Another curious thing - I can't see why an error is being thrown when type coverting "29/04/2003" from a string to a date. Unless an American date format is being expected, of course (no '29th' month!).

Hmmm...(scratches head)...it seems this should work - I'm not going to rest easy until I find-out what the issue is!

Cheers, again for the input.

SKING

This might be the problem.
What is the database charset?

I'm not sure. I checked the properties of the database within IBExpert. There are several properties listed, but nothing under 'Server', 'Role' and 'Charset' - just blank fields - no entries.

IBExpert is the only tool that I have used to work with Firebird. Is there anything that can be done from the command line to directly interrogate the database and determine this information?

Thanks again,

SKING