I’m sorry, I forgot to mention the database is Dialect 3 (and of course TIME
is only available in Dialect 3).



What I don’t understand is why do you get an error setting one declared
“TIME” data type equal to another declared “TIME” data type? The source
variable contains a valid time. The error message complains about it like
you are trying to typecast a string to a time. I have no trouble figuring
out how to typecast a time string into a time variable.



You bring up a good point, Helen, about the time going past the 24 hour
mark. The query that I am running was originally written with timestamps
and it worked fine, I decided to play with the time data type for simplicity
but I do need to get into the next day a few hours so I will be going back
to what I originally was doing. I thought this might be a place to learn
about the Time data type and it looked like it would make the coding cleaner
and easier.



Thanks



Jerry



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Thursday, February 05, 2009 9:00 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] TIME datatype



At 01:02 PM 6/02/2009, you wrote:

>I have worked with Firebird / Interbase for about 12 years now and I must
>admit I am stumped. I am writing a stored procedure that uses “TIME”
>fields. I want to initialize a begin and end time and I keep getting
>errors. I wrote as simple a stored procedure as I could to illustrate the
>problem. I am setting up a StartTime and need to add an hour to the time
>and store as an end time. I cannot get it to work and here is an example of
>just setting EndTime = StartTime. I am using Firebird V1.5.5.4926.
>
>What am I missing here?

Well, first of all, if your database is SQL dialect 1 then the TIME type
isn't available. That will be the first problem to iron out.

>CREATE PROCEDURE TP
>
>AS
>
>DECLARE VARIABLE Starttime TIME;
>
>DECLARE VARIABLE Endtime TIME;
>
>begin
>
> StartTime = '08:00';
>
> EndTime = StartTime; ß error occurs on this
>
>end

Old InterBase versions used to convert datetime literals in a fairly
unreliable manner. It got cleaned up at v.6 and you need to cast time
literals in a lot of places where IB 5 and lower didn't require it.

Note that you usually need to apply the full 24-hr clock 'hh:nn:ss.zzz' mask
to the time literal.

CREATE PROCEDURE TP
AS
DECLARE VARIABLE Starttime TIME;
DECLARE VARIABLE Endtime TIME;

begin
StartTime = cast ('08:00:00.000' as TIME);
/* You can also use a quick-cast with date/time literals
StartTime = TIME '08:00:00.000' ; */
EndTime = StartTime;
end

and then, when you want to add an hour:
EndTime = StartTime + (60*60);

But figure out whether you really want to use a TIME type for this. What are
you going to do if your start time is '23:00:00.000' or higher and you try
to add an hour to it?

./hb





[Non-text portions of this message have been removed]