In DBISAM I would set all tables to the ANSI standard language. Then
all date values in SQL WHERE clauses needed to be in ISO format. In
EDB I am getting #700 Expected Date or Timestamp expression errors
using ISO date format or the format of my system. What datetime format
do I use?

Michael,

<< In DBISAM I would set all tables to the ANSI standard language. Then all
date values in SQL WHERE clauses needed to be in ISO format. In EDB I am
getting #700 Expected Date or Timestamp expression errors using ISO date
format or the format of my system. What datetime format do I use? >>

EDB uses the  SQL 2003 standard for date/time literals:

http://www.elevatesoft.com/edb1sql_date_time_types.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

On Sat, 26 May 2007 18:41:09 -0500, "Tim Young [Elevate Software]"
<timyoung@elevatesoft.com> wrote:

>Michael,
>
><< In DBISAM I would set all tables to the ANSI standard language. Then all
>date values in SQL WHERE clauses needed to be in ISO format. In EDB I am
>getting #700 Expected Date or Timestamp expression errors using ISO date
>format or the format of my system. What datetime format do I use? >>
>
>EDB uses the  SQL 2003 standard for date/time literals:
>
>http://www.elevatesoft.com/edb1sql_date_time_types.htm

OK, thanks Tim. I do see an oddity though. If you have a table with a
timestamp field you can't seem to use a BETWEEN query with dates, only
timestamps. When I run a query like this:

select * FROM MyTable WHERE MyTimeStampField BETWEEN DATE '2007-02-15'
and DATE '2007-05-17'

I get:

ElevateDB Error #700 An error was found in the statement at line 1 and
column 48
(Expected Timestamp expression but instead found DATE '2007-02-15')

I don't know if that SQL is standard or not, but it does work in
MySQL.

Michael,

<< OK, thanks Tim. I do see an oddity though. If you have a table with a
timestamp field you can't seem to use a BETWEEN query with dates, only
timestamps. When I run a query like this: >>

Hmmm, for now you'll have to CAST the constants as TIMESTAMPs in order to
get it to work, or phrase the constants as TIMESTAMPs.  I've got to think
about this one some more, but I will probably correct it so that it doesn't
require the CAST().

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Hmmm, for now you'll have to CAST the constants as TIMESTAMPs in order to
get it to work, or phrase the constants as TIMESTAMPs. >>

I think that's the way it should be, since TIMESTAMP and DATE values are not
comparable.

Ole Willy Tuv