sub: MS SQL server and (missing) ANSI DATE-datatype
I’m working at a company who has developed a large client/server application in Delphi 6, with Interbase as the DB-server (both Borland products). As data-access technology we use dbExpress (from Borland) because it is claimed to be database-independent. Now we have a new customer who wants to use MS SQL-server and I investigated if this was possible. Unfortunately my conclusion is that it is not (at least not for us), as I’ll explain below. If anyone thinks I’m wrong, and so has a solution, I’d be very pleased to hear it.
First let me repeat the mail I wrote to Borland:
-- We have a MS SQL Server database and we use dbExpress to connect to it (with driver dbexpmss.dll, vendorLib OleDb). When we want to use a query and it has a date-time parameter, we get the error:
“EdatabaseError: dbExpress Error: Invalid Field Type.”
I found a work-around, which in brief says: use TSQLTimeStamp in stead of TdateTime (see http://bdn1.borland.com/article/0,1410,30078,00.html), and indeed this works. The only problem is that we already have a large application which contains a lot of these kind of parameterized queries, so it would take a lot of time to re-program. Our application works fine with Interbase and Firebird as database, but now we have a new customer who wants to use MS SQL-server. When we started building our application, we chose for dbExpress because it claimed to be database-independent, but apparently it’s not. In another similar bug-report (see report 11090) I read that Borland gave this bug the status ‘closed’, but to really forfill the claim about database-independence, this bug should be fixed. –
Although Borland didnt’s answer me (yet), I think they consider this a non-issue (for them) because the work-around they suggest is imposed by Microsoft, because SQL-Server does not comply to the SQL ANSI-standards regarding date/time-values: Interbase has a DATE-datatype (as prescribed by ANSI), but SQL-Server has not, it only has DATETIME (in ANSI this is called TIMESTAMP).
I read somewhere (in an article of 2003) that the next version of SQL-Server (code-name Yukon) would be extended with DATE-type, but in SQL-Server 2005 I didn’t see it yet.
Why can’t Microsoft just comply to ANSI-standards..