Subject Re: [firebird-support] Extracting Time
Author Helen Borrie
At 11:54 AM 26/06/2003 +1000, you wrote:
>I have a Firebird dialect 3 db with TIMESTAMP fields.
>I need to get just the time from these fields. I actually want to format the
>date part of the field separately and concatenate it badk with the time part
>of the field.
>F_STRIPTIME returns a date (as expected) but F_STRIPTIME also returns a DATE
>(being the time cast as a date it appears and therefore 31.12.1899).

Then you are looking at a Delphi casting of "Day zero". This will be a
number like 0.nnnnnnn (day zero from Firebird's p.o.v. would be midnight on
November 17 189-something, but anyway, not the same zero-day as
Delphi's,which you are looking at above). Your problem here is that Delphi
doesn't have a TIME type so you have to cast Firebird's TIME type
AsDateTime and define a display mask that doesn't look at the zero on the
left side of the decimal point. You can use the display mask to hide the
milliseconds too.

>How do I get just the time from this field type - I've also tried many cast
>attempts with no success other than truncation errors

In dialect 3 you can do
SELECT CAST (MyTimeStamp as TIME) as TimeOfDay, which will return a
time-only value. You will get errors if you try to cast a DATE value as
TIME, obviously. You can also use casting to extract a TIME and save it
into a time type column, e.g.

values (cast(:Timestampvalue as time))