Subject Any way to use an input parameter in an order by clause
Author Adam
Hi all,

I have some records being returned that contain a timestamp field. I
would like to return the records ordered by the difference between
this field and a timestamp that I will pass in as a parameter. This
will be inside a stored procedure.


select *
from sometable
order by ABS(somefield - :someparameter)

In theory, it should return the record closest to someparameter first,
followed by the second closest etc, except I can't seem to get it to
work even with casting

The following query does indeed work as expected, it is only when I
replace the 'hard coded' timestamp with a parameter I get an issue.

select *
from sometable
order by ABS(somefield - cast('1/1/2006 9:00' as Timestamp))

I have also tried moving this logic into the select with the intention
of using the order by [fieldnumber] syntax, but it has the same problem.

I have tried explicitly casting everywhere with no luck.

Dynamic SQL Error
SQL error code = -804
Data type unknown.

FB 1.5.3 CS / WinXP.

Is there something I am missing here, or is it something that can't be
done without wrapping it in a stored procedure and manually
calculating it as an output parameter?