At 02:02 PM 5/15/01 +0100, you wrote:
>I can see what you mean - I can reproduce it here.
>If you use the selection expert and select on a date you end up with some
>SQL that looks like:
>
>SELECT
> Salesinf.`empid`, Salesinf.`xtransdate`
>FROM
> `Salesinf` Salesinf
>WHERE
> Salesinf.`xtransdate` = {d '1996-11-23'}
>
>Crystal does the same sort of thing against a SQL Server 7 datasource,
>although SQL Server understands the syntax.
>From what I've read, the date expression referred to above, ie "(d
'1996-11-23')" is referred to as ODBC standard date format, and there
exists in ODBC specifications similar syntax for expressing time literals,
eg "(t '12:03:00')", and timestamp (datetime) literals, eg "(ts '1996-11-23
12:03:00')". One author writes "These extensions are aimed at making ODBC
SQL more portable across data sources by providing a uniform syntax for
features which vary across database systems. The most commonly used ODBC
SQL extensions include those defined for date/time specification, scalar
functions, and stored procedures." In another context, the Microsoft web
site refers to this syntax as "ANSI standard date format."
For this unifying scheme to work of course, both drivers on either side of
the ODBC layer have to support it. But Crystal is not alone in identifying
its date and time literals this way-- Cold Fusion, SQL Server, MySql, and a
number of other products appear to support it. When using Crystal to pass
a query to an ODBC driver that doesn't support this syntax, you can write
the appropriate query directly in its SQL Syntax window.
David B
