At 10:35 a.m. 22/06/2013, Craig Cox wrote:

>I am using FB 2.5 to query information from three tables, and calculate the number of days from a previous date held in a column and today. Can you provide an example?
>
>This code does not work :(

It's always a good idea to state HOW it doesn't work (error message!)

>select
> w_containers.container_id,
> w_shipments.begin_fill_date,
> w_waste_streams.waste_stream,
> w_containers.number_of_containers,
> datediff(day, (today), w_shipments.begin_fill_date)
>from w_shipments
> inner join w_waste_streams on (w_shipments.waste_stream_pk = w_waste_streams.waste_streams_pk)
> inner join w_containers on (w_shipments.container_pk = w_containers.container_pk)
>where
> (
> (w_shipments.begin_fill_date is not null )
> and
> (w_shipments.shipping_begin_date is null )
> )
>order by w_shipments.begin_fill_date

Your expression isn't syntactically correct. 'TODAY' is a string literal and needs to be in single quotes, not brackets. What's more, in some contexts (maybe this one) you need to CAST ('TODAY' AS DATE).

But the context variable CURRENT_DATE is SQL standard for the same thing and is easier to work with:
datediff(day, CURRENT_DATE, w_shipments.begin_fill_date)

Just make sure that begin_fill_date is a DATE type, not a TIMESTAMP. If the latter, then you should cast it as DATE to avoid getting hung up on part days; or use CURRENT_TIMESTAMP if you want the part days in the difference.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________