At 10:55 AM 02-08-02 -0700, you wrote:

>I'm using CONTAINING to search any field in my table. On a TIMESTAMP
>field I have determined how to search on almost everything except for
>a date which includes the year.
>
>For example if my field contains: 01/02/2003 14:05:06
>I can search on:
> Fields: 01, 02, 03, 2003, 14, 05, 06
> Time: 14:05, 14:05:06, 05:06
> M & D: 01-02
>
>But I have not determined how to include the year. I have tried the
>following without success:
> 01-02-03
> 01-02-2003
> 01-02-103
> 03-01-02
> 2003-01-02
> 103-01-02
>
>
>Does anyone know of how to include the year for such a search?
>Does someone the source available to see what format can be used?

The TIMESTAMP type (along with the other date/time types) is not a string,
it is a numerical value. Therefore, when searching on date/time types, the
criteria must evaluate to numbers, not strings.

Date literals (which look like strings) can be used in search expressions
involving complete date or date/time types. In some situations, you need
to CAST date literals. However, because the engine does not interpret date
literals as strings, it is not valid to use CONTAINING, LIKE or STARTING
WITH in a date search.

An example of a timestamp search expression would be
...WHERE MyStoredTimestamp BETWEEN '2002-01-01' AND '2002-08-03'
...WHERE MyStoredTimestamp > '31.12.2001' AND MyStoredTimestamp < '2002/04/08'
or
...WHERE MyStoredTimestamp > '31.12.2001' AND MyStoredTimestamp < 'TOMORROW'

In the above examples, you see three (of a larger set) of possible date
literal representations. ('YESTERDAY', 'TODAY', 'TOMORROW' and 'NOW' are
special date literals...). The context variables CURRENT_TIMESTAMP,
CURRENT_DATE and CURRENT_TIME are also available.

Note that you have to CAST date literals in INSERT and UPDATE expressions, e.g.
UPDATE ATABLE
SET MyTimestampCol = CAST ('16.05.2002' AS TIMESTAMP) ;

The EXTRACT() function in expressions allows you to look at individual
components of a date/time type as integers. For example,

EXTRACT (YEAR FROM CURRENT_TIMESTAMP) would evaluate to 2002 and EXTRACT
(MONTH FROM CURRENT_TIMESTAMP) would evaluate to 8.

You could, for example, do searches with
...
WHERE EXTRACT (YEAR FROM MyTimestampCol) = 2002
or
WHERE EXTRACT (YEAR FROM MyTimestampCol) BETWEEN 1999 AND 2002

This isn't a comprehensive guide to handling date/time types in expressions
- you really need to study the Data Definition Guide and the Migration
Guide to get the whole story on this topic, including the supported
date/time literal formats.

heLen

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