SQL Commands

[villanrl]

Hi Oleg.I'm interested in the SQL commands that are supported in robotask.  I've been trying to use dates in my select statment but with no luck.Thanks.  Russ V.

[Oleg]

Can you send the example of SQL statement, that you want to use? Also, what kind of database do you use?

[villanrl]

First I'm using Crystal Reports to generate the SQL statement.
Here is one that works that I believe is on a windows server going through ODBC.
SELECT dbo.ARCServices.*
FROM dbo.ARCServices
WHERE dbo.ARCServices.RecordStatus='i' AND
dbo.ARCServices.CreatedDate > '1/1/2006';

Next is a SQL table on a Unix machine going through an ODBC connection.
It works except when adding the WHERE clause
The error is:
E: 12/21/2006 1:26:14 PM: Driver]Expected lexical element not found: <identifier>

 SELECT "CHG_Change"."Category", "CHG_Change"."Create-date"
 FROM   "CHG:Change" "CHG_Change"
 WHERE  "CHG_Change"."Create-date">={ts '2006-10-01 00:00:01'}
If I change the WHERE to be
 WHERE  "CHG_Change"."Create-date">'1/1/2006'
then no records are returned.
Any Ideas?
 

[Oleg]

Try to write so:

SELECT dbo.ARCServices.*
FROM dbo.ARCServices
WHERE dbo.ARCServices.RecordStatus='i' AND
dbo.ARCServices.CreatedDate > #1/1/2006#

You must write dates in SQL statement in such format:
for european format #DD/MM/YYYY#
for american format #MM/DD/YYYY#

The order of days and month number must be corresponding to your regional settings. See Control panel -> Regional and language options