[Zope] Field type 'Date' in SQL request

[Matthew T. Kromer]

Dieter Maurer wrote:

>Matthew T. Kromer writes:
> > Here's the action from the Oracle error message guide:
> > 
> > > Action: Enter a valid column name. A valid column name must begin with 
> > > a letter, be less than or equal to 30 characters, and consist of only 
> > > alphanumeric characters and the special characters $, _, and #. If it 
> > > contains other characters, it must be enclosed in double quotation 
> > > marks. It may not be a reserved word.
> > 
> > I read that as being that you can't use TO_CHAR() as a column name in 
> > your where clause; the TO_CHAR must be on the right-hand-side of the 
> > expression e.g.
> > 
> >    where
> >        DATE_NAI_IND = TO_DATE('08/08/1997', 'dd/mm/yyyy')
> > 
> > although normally Oracle will do string to date conversion for you 
> > automatically.
>I do not think, this is the problem::
>
>  in order to implement case insensitive search, I used:
>
>     where ... lower(column_name) like/= lower(literal) ...
>
>  and Oracle did not complain. I expect, the left side of
>  "=/like" is not restricted to a column name.
>
>
>Dieter
>

Yep, I just re-read his original question... the problem is he was 
quoting the date wrong!

He said:

    where TO_CHAR(DATE_NAI_IND,'dd/mm/yy')= "08/08/1997"

The problem is he used double quotes!  String literals need single 
quotes in SQL; using a double quote means referencing a schema name -- 
and of course, no fully qualified can include a slash.

So,

    where  TO_CHAR(DATE_NAI_IND,'dd/mm/yy')= '08/08/1997'

should work.

-- 
Matt Kromer
Zope Corporation  http://www.zope.com/