<hannes@...> wrote in message news:9o7avo+eb2e@......

> hello IB developers,
> is there anything changed with the internal format
> of DATE or TIMESTAMP of 'now' and 'today'?
>
> I tried the f_year('TODAY') function of my old UDF and got
> unbelievable results. the same with Greg Daetz's new FreUDFlib.

Hello, Hannes, greetings from inside the engine, specifically from the DSQL
parser that handles SQL syntax.
:-)

> Age of persons in
> computed (f_Year('TODAY') - f_year('BDate'));
> returns values like 24548 years :-)

select f_year('today') from rdb$database
=> -1404088
select f_year('TODAY') from rdb$database
=> 60050
select f_year(cast('today' as date)) from rdb$database
=> 2001
select f_year('now') from rdb$database
=> 23289
select f_year(cast('now' as timestamp)) from rdb$database
=> 2001
select f_year(current_date) from rdb$database
=> 2001
select f_year(current_time) from rdb$database
=> -1156855
select f_year(current_timestamp) from rdb$database
=> 2001
select f_year('Hannes') from rdb$database
=> -805155

UDFs that handle DATE were created when DATE was date+time. Now, in dialect
3, finally, DATE is date-only, TIME is time-only and TIMESTAMP is date+time.
Greg's UDF expects an old timestamp, so can see above that passing either a
timestamp or a date works, but of course passing a time-only is undefined.

The old string constants (that I personally would deprecate, because they're
a headache) like 'today' and 'now' should be casted to a proper data type to
be usable. Otherwise, they are interpreted as literal strings and you get
garbage in the UDF. It seems that the DSQL parser was stuffed when dialect 3
support was included. Hence, your old tricks in IB5 may not work anymore,
unless you use an explicit cast. For example:

select extract(year from 'today') from rdb$database
=> Dynamic SQL Error
SQL error code = -105
Specified EXTRACT part does not exist in input datatype

select extract(year from cast('today' as date)) from rdb$database
=> 2001
select extract(year from current_date) from rdb$database
=> 2001

Finally, why are you still using UDFs for this purpose if IB6 and Firebird
have the built-in, ANSI function EXTRACT???

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing