get ORA-00907 error when using dateformat DD.MM.YYYY HH24:MI:SS

[punkicat]

Hello,

I always get the error ORA-00907 (missing right parenthesis) when I use a date-format like DD.MM.YYYY HH24:MI:SS.
When I use the date-format DD.MM.YYYY (without hours:minutes:seconds) the Query works as expected.

code-example:

with Glx_DataModule.OraQuery_GlxLIMSData do begin
SQL.Clear;
KeyFields := '';
KeySequence := '';
SelectClause := 'SELECT g.doc_id,g.version_num,g.modified,g.systemname,.....'
FromClause := ' FROM glx_lims_data g, sm_test t';
OrderClause := ' ORDER BY g.modified desc';

// works fine!!!
WhereClause := ' WHERE (t.test_number = g.wa_test) AND (G.PROCESSDATE > '15.04.2008');
// fails with ORA-00907!!!
WhereClause := ' WHERE (t.test_number = g.wa_test) AND (G.PROCESSDATE > '15.04.2008 12:30:00');
// fails with ORA-00907!!!
WhereClause := ' WHERE (t.test_number = g.wa_test) AND (G.PROCESSDATE > to_date('15.04.2008 21:48:13','DD.MM.YYYY HH24:MI:SS')

SQL.Add(SelectClause);
SQL.Add(FromClause);
SQL.Add(WhereClause);
SQL.Add(OrderClause);

FetchAll := true;
Prepare;
Open;

...

Close;
end;

ODAC-Version: 5.80.0.42 with Delphi 2006, Oracle 10, net-mode=true

How can I use the full datetime-format?

Thanks.

[Plash]

It is better to use syntax with to_date function.

The error is raised because you have missed right parenthesis.

The following line should be correct:

Code: Select all

WhereClause := ' WHERE (t.test_number = g.wa_test) AND (G.PROCESSDATE > to_date(''15.04.2008 21:48:13'',''DD.MM.YYYY HH24:MI:SS''))';