Date formats in SQL Server

rbuser

Would it be possible to change the default formatting for MS SQL Server
dates in daSQL.pas? In the function TdaSQL.ResolveCriteria there is a line:

if IsMSAccess then
Result := '#' + Result + '#'
else if not (IsOracle) then
Result := '''' + Result + '''';

Like Oracle, SQL Server should not have the single quotes surrounding it so
can this be amended to:

else if not (IsOracle or IsMSSQLServer) then
Result := '''' + Result + '''';

Also in daDB.pas, the function GetSearchCriteriaDateFormat has the code:

case aDatabaseType of

dtMSAccess:
Result := 'YYYY-MM-DD';

dtMSSQLServer, dtSybaseASA, dtSybaseASE, dtOracle:
Result := 'YYYY/MM/DD';

I have amended my ODBCExpress DADE plug-in for RB 6.03 to read:

dtMSAccess:
Result := 'MM/DD/YYYY';

dtMSSQLServer:
Result := '"{ts ''"YYYY-MM-DD" 00:00:00''}"';

Many thanks,
Jason.

Jason Sweby
Software Developer
Carval Computing Limited, Plymouth, UK.

digitalmetaphors

Is it not working for you? It works for us here when we connect to SQL
Server using ADO and the BDE.

You can change the source to change how the formatting is done, but it does
this because SQL Server expects this format and so we always convert it.

The GetSearchCriteriaDateFormat is overridable in your DADE plugin for the
TdaSession class descendent.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

It's probably because I'm going through the ODBC to SQL Server; ADO and ODBC
obviously use different date formats in the SQL. I've overridden the date
format in my DADE plug-in (for ODBCExpress), but the quotes around the query
in daSQL.pas as outlined below cause it to fail, which is why I requested
the code change:

else if not (IsOracle or IsMSSQLServer) then
Result := '''' + Result + '''';

Thanks,

Jason Sweby,
Carval Computing.

digitalmetaphors

Haven't tried ODBC to test this. The database itself should control the
date format that it expects. The database ocnnectivity should simply pass
through to the database. From MSDN:
SQL Server recognizes date and time data enclosed in single quotation marks
(') in these formats:

a.. Alphabetic date formats (for example, 'April 15, 1998')


b.. Numeric date formats (for example, '4/15/1998', 'April 15, 1998')


c.. Unseparated string formats (for example, '19981207', 'December 12,
1998')

If you use ADO, does the same date format work on your database?


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Jim,

I tried the following query to an SQL Server 2000 database in a test program
through the ODBC:

SELECT Surname, Date_of_birth FROM Employee WHERE Date_of_birth =
'4/15/1975'

This was the error I received, which was the same one I was getting in RB:

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime value.

However, all the other formats you suggested worked. When I tried the first
one again in British date format (i.e. dd/mm/yyyy instead of mm/dd/yyyy to
give 15/4/1975, it worked).

I will revise my ODBCExpress plug-in to use one of these date formats and
see how I proceed.

Thanks for your time,
Jason.

rbuser

Hi Jim,

If it's of any help to you or Jason Sweby, we had overridden
GetSearchCriteriaDateFormat in version 6, to make the dates work (UK) by
setting the result to 'dd/mm/yyyy', but in version 7 you've hard coded the
conversion into daSQL.pas now, so we've had to remove the override to make
it work again.

(Using MSSQL server and daDbOvernet.pas).

Regards,
Dave.