Is the ElevateDB SQL syntax for using dates, times and timestamps standard?

My application has the ability to switch between database back-ends which
means that I try to use 'standard' SQL as much as possible in order to avoid
having to code engine-specific SQL.

In PGSQL, DBISAM and MS SQL server, the following code is pretty standard :-

  UPDATE tbl SET timestampfield = '2009-1-1 09:00';

ElevateDB however requires a TIMESTAMP prefix, e.g. :-

  UPDATE tbl SET timestampfield = TIMESTAMP '2009-1-1 09:00';

The same is true of DATE and TIME fields :-

  UPDATE tbl SET datefield = DATE '2009-1-1';
  UPDATE tbl SET timefield = TIME '09:00';

Are these prefixes standard SQL, or is this an ElevateDB deviation from the
standard? If it's a deviation, then is there any way of perhaps relaxing
this at all please so that we can omit the prefixes? Due to the nature of my
application (i.e. switching between back-ends), this is causing me quite a
few difficulties!

Many thanks!

Hedley


I think its sql2003 standard but not prior to that - if you think that's a problem wait until you get to date arithmetic and INTERVAL

I think DBISAM was sql99 ish (might have been 89 or other date in the 20th century) so the syntax as used in ElevateDB has definitely changed in areas.

Roy Lambert [Team Elevate]

Hedley,

<< Is the ElevateDB SQL syntax for using dates, times and timestamps
standard? >>

Yep, SQL 2003.  Here's a link to some comparisons between different DBs and
the standard:





>
> My application has the ability to switch between database back-ends which
> means that I try to use 'standard' SQL as much as possible in order to
> avoid having to code engine-specific SQL.
>
> In PGSQL, DBISAM and MS SQL server, the following code is pretty standard
> :-
>
>   UPDATE tbl SET timestampfield = '2009-1-1 09:00';
>
> ElevateDB however requires a TIMESTAMP prefix, e.g. :-
>
>   UPDATE tbl SET timestampfield = TIMESTAMP '2009-1-1 09:00';
>
> The same is true of DATE and TIME fields :-
>
>   UPDATE tbl SET datefield = DATE '2009-1-1';
>   UPDATE tbl SET timefield = TIME '09:00';
>
> Are these prefixes standard SQL, or is this an ElevateDB deviation from
> the standard? If it's a deviation, then is there any way of perhaps
> relaxing this at all please so that we can omit the prefixes? Due to the
> nature of my application (i.e. switching between back-ends), this is
> causing me quite a few difficulties!
>
> Many thanks!

Hedley,

Let's try that again:

<< Is the ElevateDB SQL syntax for using dates, times and timestamps
standard? >>

Yep, SQL 2003.  Here's a link to some comparisons between different DBs and
the standard:

http://troels.arvin.dk/db/rdbms/#data_types-date_and_time

It's been this way since at least SQL 1999, and probably earlier.  It's the
only way to allow the SQL parser to unambiguously determine that you're
specifying an actual date/time/timestamp instead of a character string that
just so happens to contain a date in string form.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


That's a really useful link - only downside is no ElevateDB

Roy Lambert

Thanks for the info Tim
<sigh>
Yet another learning curve to hike up!

Hedley,

<< <sigh>Yet another learning curve to hike up! >>

Yeah.  One interesting thing I noticed at that link was that the author
indicated that PostgresSQL uses the SQL standard TIMESTAMP '<literal>'
syntax also.

--
Tim Young
Elevate Software
www.elevatesoft.com