Subject RE: [firebird-support] error formating date in stored procedure
Author Alan McDonald
> 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). 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.
> 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 ??
> Thanks I'd be eternally grateful
> Chris B

use substring(mydate from 0 for 2)||'-'||substring(mydate etc
then cast it as a date