At 11:09 AM 11-07-02 -0500, you wrote:

>I apologize for the vagueness of my last post. I have since narrowed down
>the source of my problem....
>
>I a running the current production version of Firebird on Win2K.
>
>In my stored proc I am inserting a row into a table that includes a
>Timestamp column. The value of this column needs to contain the current
>timestamp (plus a small adjustment of a few seconds as determined by logic
>in my stored proc). I have accomplished this by using the
>'current_timestamp' variable and doing some date arithmetic. So far so good.
>
>My problems begin when I try to set the fractional seconds portion of this
>value to '0000'. I cannot afford to have non-zero fractional second
>precision in the table because it appears that Interclient does not
>support it (Any select that my JDBC app does to this data does not appear
>to retrieve the fractional seconds).
>
>In my stored proc I have tried Casting Timestamp variable to Char and then
>manipulate it but no luck. Is this the best approach? Does anyone have
>suggestions or stored proc code samples on how I can have a Timestamp
>value inserted with '0000' in the fractional seconds?

You are getting the overflow error because the timestamp is *already*
stored complete with the fractional seconds. If you used CURRENT_TIMESTAMP
to acquire the timestamp, the frac seconds will already transliterate to
'.0000'.

Experiment with this on your system:

create table testts(
ts timestamp,
ts_char varchar(99)) ;
commit;

insert into testts (ts)
values(CURRENT_TIMESTAMP);


insert into testts (ts)
values('25-DEC-2002 23:59:59.4444');

commit;

update testts
set ts_char = cast(ts as varchar(99));

From this you will be able to determine exactly how long your CHAR()
column needs to be. On my system it delivers 24 characters when casting a
timestamp to varchar, e.g.

2002-12-25 23:59:59.4444
2002-07-12 09:30:23.0000 (for CURRENT_TIMESTAMP)

regardless of the literal format I use for input. What you see may vary
because of different environment settings.

For more information about the formats available for date literals, see the
relevant section of the Data Definition Guide. The "Using Firebird" manual
(if you have the IBPhoenix CD) has more information and examples.

cheers,
heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________