At 01:01 AM 8/08/2007, you wrote:

>Hi I'm repeatedly getting the following error message
>
>Overflow occurred during data type conversion.Conversion error from
>string "16/09/2004".
>
>when I try to run a simple stored procedure that takes a varchar(60)
>from one column in a table and then does an
>
>INSERT INTO mytable (...) VALUES (..., :str_date);
>
>to store that string as a date.
>
>now after looking through the various newsgroups and assorted posts, I
>know that I need to somehow convert that string from dd/mm/yyyy into
>mm-dd-yyyy so that firebird can understand it. (Something I've done in
>delphi more than once).

See http://www.firebirdsql.org/doc/contrib/FirebirdDateLiterals.html
for a list of valid date literals. The dd mm yyyy format will be
valid if you replace the "/" separators with ".".

>But I've got no idea how to do this inside a
>firebird stored procedure. I've found lots of vague references to
>various udf functions and I've tried getting the CAST or EXTRACT
>functions to work, but with no success.

You won't get a valid CAST or EXTRACT if the date literal isn't valid.

>I'm working on a tight deadline and I've got to get this sorted, so it
>could just be due to the fact I'm panicking that I'm missing something
>really obvious. but could some-one spell out how to do the equivalent
>of a FormatDateTime('mm-dd-yyyy',aDate) in a firebird stored procedure
>to convert a string to a date for me ??

That would be a Big Ask, i.e. a pretty complex UDF.

If you are certain that the string has a consistent format of
(d)d/mm/yyyy then you could write a simple routine to replace the
slash characters with dots (not hyphens). Then you will be able to
cast the string as DATE.

You'll need to have the STRLEN() UDF declared in your database.

CREATE PROCEDURE ConvertDate (
inputstring varchar(16) /* Why 16? */ )
RETURNS (
ddate DATE)
AS
declare dstring varchar(10) = 'NULLDATE';
declare thischar char;
declare counter smallint = 10;
BEGIN
if (STRLEN (inputstring) >= 9) then
dstring = '';
if (STRLEN (inputstring) = 9) then
counter = 9;

while (counter > 0) do
begin
if (STRLEN (inputstring) = 0) then
begin
counter = 0;
LEAVE;
end

thischar = SUBSTRING (inputstring FROM 1 FOR 1);
if (thischar = '/') then
begin
thischar = '.';
-- prepend zero if day is single digit
if (STRLEN (dstring) = 1) then
dstring = '0' || dstring;
end
dstring = dstring || thischar;
inputstring = SUBSTRING (inputstring FROM 2);
counter = counter - 1;
end
if (dstring = 'NULLDATE') then
ddate = NULL;
else
ddate = CAST (dstring AS DATE);
END