At 04:23 PM 08-11-02 -0600, you wrote:

>Hi,
>
>I would like to extract the 4 digit year from a date. In another database I
>work with I have lots of date functions to do this type of thing but I don't
>seem to have these types of options with Firebird v 1.0. For example, using
>Red Brick I can get the year by a couple of different means, like
>
> EXTRACT(YEAR from INVOICE_DATE)
>
>How would I do something like this in Firebird?

Exactly the same in Firebird.

>Another thing I would like to be able to do is put a default constraint on a
>date columns that puts the system date in the column if no value is
>provided. How can I do this with Firebird?

CREATE TRIGGER BI_ATABLE FOR ATABLE
ACTIVE BEFORE INSERT
AS
BEGIN
IF (NEW.LATEST_DATE IS NULL) THEN
NEW.LATEST_DATE=CURRENT_TIMESTAMP;
END

Then, to auto-update this column on updates, do this:

CREATE TRIGGER BU_ATABLE FOR ATABLE
ACTIVE BEFORE UPDATE
AS
BEGIN
NEW.LATEST_DATE=CURRENT_TIMESTAMP;
END

>I'd also would like to define a check constraint where column must be either
>ValueA or ValueB. The language reference isn't very clear on this. The
>reference says something like:
>
> CHECK(search_condition)
>
>but doesn't provide my guidance on what search_condition can be.

CHECK (Value in (ValueA, ValueB))

>Finally, I am surprised that the SUBSTR(expr, stort, length) function is not
>supported. I don't know if it's part of the SQL standard but every other
>dbms I've worked with has supported SUBSTR. Is the capabilities of SUBSTR
>implemented in some other way?

Yes. Fb/IB have a very minimal set of internal functions, in order to keep
the size of the engine small and reduce the overhead of supporting
functions that nobody ever uses.

Instead, you access user-defined functions (UDFs) which live outside of the
engine in a shared library.

Firebird comes with two standard UDF libraries - ib_udf and FBUDF (.dll on
Windows, .so on other platforms) - except, currently, FBUDF is available
only on Windows... ib_udf has a SUBSTR() function. Another library that
is widely used is FreeUDFLib, which you can pick up from www.ibphoenix.com
or www.cvalde.com. It has a better range of string functions. Other
free-to-download libraries are linked from the IB Phoenix website.

Read the release notes under the UDF topic and, for the rest, read the
User-Defined Functions chapter in the Language Ref.

Can I suggest to you that you set aside some time to really explore the
resources that are around? The ibphoenix site has downloads or links to
just about anything, including Borland's beta docs for IB 6 - still the
only free manuals you can get. There is a vast searchable knowledgebase
there too.

Also consider the value of subscribing to the IBPhoenix CD, which will give
you access to the Firebird manuals, published by them.

heLen