Re: datetime fields, take two
Jon Drukman wrote:
>
> does anybody have a list of functions that are available for querying
> DATETIME fields? i already knew about year(), month() and day(). i was
> hoping there would be a corresponding hour(), minute() and second() but
> there doesn't appear to be. at least, when i try: select * from table
> where hour(datetimefield)=13, i get 674: Procedure (hour) not found.
>
> please someone, tell me it is possible to query on the time part, or i'm
> really screwed!
>
> --jon
Hi Jon,
you can try to use the following stored procedure:
CREATE PROCEDURE hour(p_dt DATETIME YEAR TO MINUTE) RETURNING INTEGER;
DEFINE l_hour DATETIME HOUR TO HOUR;
DEFINE r_hour INTEGER;
DEFINE s_hour CHAR(2);
LET l_hour = EXTEND (p_dt, HOUR TO HOUR);
LET s_hour = l_hour;
LET r_hour = s_hour;
RETURN r_hour;
END PROCEDURE;
If your datetime field is defined as something different than year to minute
you have to change the definition of p_dt.
Regards
Tolis
+---------------------------------------------------------------------+
| V+K Relational Solutions email: tvarnas@compulink.gr |
| Deligiorgi 26 tvarnas@orbit.de |
| 546 42 Thessaloniki Voice: (30) 31 820270 |
| Greece Fax: (30) 31 865463 |
+---------------------------------------------------------------------+