Re: Problem with dates in select

Volker Schmid wrote:

> Hi,
>
> The User inputs: ColumnName, Operator (=,<,>,!=) and a Value.
> In SQL-Server I used this to create an SELECT-statement with the user input:
>
> If the value is a date and the column is also date i created the following:
>
> SELECT * FROM Objects WHERE DateDiff(day, Columnname, 'UserValue') = 0
>
> The '=0' changes with the choosen operator.
>
> How can I do this in Oracle?
>
> SELECT * FROM Objects WHERE TRUNC(Columnname - TO_DATE('UserValue',
> 'DD.MM.YYYY')) =0;
>
> works great. But if I use < or > it doesn't do good because a truncated
> result of -0.35 or 0.67 is always 0! So it's not working in every cases.
>
> Please give me tips or a good solution.
>
> The same problem occurs in future if I try to use between, or?
>
> Thanks,
>
> Volker

I do not understand your statement:

"But if I use < or > it doesn't do good because a truncated result of -0.35 or 0.67 is always 0! So it's not working in every cases."

An example of two would be nice.

I wonder whether you are aware of the fact that Oracle date fields always contain the time unless they have been truncated. You might find the following works better than what you are doing.

SELECT * FROM Objects WHERE TRUNC(Columnname) - TO_DATE('UserValue','DD.MM.YYYY') =0;

Daniel Morgan Received on Wed Oct 23 2002 - 10:29:41 CDT