Big problem with Access DateTimeFields

[glk70]

I'm using a MSaccess database thorugh ADO interface via Zeosib

Executing a query with DastTime fields I always get only the date (integer) portion of the field.
I've followed the problem as far as I could and I've seend that the field type returned is a STdate, which is converted into a TDate.
TDate and TDateTime are just the same type in Delphi, yet the contet of the field is always an integer. S only the date portion of the field is returned.

I obviously need the time portion also.
Looks like the ADO provider is returning only the date portion of the field.

Any suggestion ?

[seawolf]

Looks like ADO activeX does not export DateTime fields.
So you coluld change, on ..\src\dbc\ZdbcAdoutils.pas

adDate : Result := stDate;

to

adDate : Result := stString;

So a datetime field is a String you can convert. The "problem" could be how that data is formatted.

[glk70]

changing the field tipe in convertion doesn't solve the problem

The real problem is : when accessing DataTime fields date is truncated, so only the integer (days) part of the datetime field is transfered to the application, the hours are lost.

at this point changing the coding of the field (tdateTime = Single, or String) doesn't change the fact that the decimals (hours) part of the field are already lost.

at this point the application will always show 00.00 in the hour fields on the form.

I changed the connector and tried to use DBGO ADO connector.
Using this connector I can read DateTime fields with hours and days.
obviously I cannot use zeos with this connector and I had to implement a new interface between this set of the components and my application

So I think it is a problem of interface with the ActiveX/Com component of the m$ ole libray.
Somehow (I don't actually know neither how nor why) when accessing the query zeos declares or forces dateTime fields to Date, and consequently all data are truncated.

workaround : convert all fields to floating point. Delphi handles dates as single floating point values.
Once floating point is saved correctly to DB it can be converted to String dateTime format in any moment.
Obviously this is not possible if working on a living project with running databases and with other applications sharing access to them.

a patch is required for this.
If I'll have more information on the problem I'll share on this forum

Regards

[seawolf]

Reading here it looks like you need to declare that field in a different way, and using Access > 98

[glk70]

the fields in access cannot be declared in that way
Field Types in Access are
Text
Memo
Numeric
Date/Time
Currency
Counter (i.e. long integer with autoincrement)
Boolean
Ole Object
HypertextLink ( ! )

What is presented in the article you are citing is what I tried to explain previuosly.
Somewhere and somehow Zeos is declaring date fields in the query as
adDate 7 Date
instead of
adDBTimeStamp 135 DateTime (Access 97 (ODBC))
Otherwise a declaring datetime fields as
adSingle 4 Single Real
would be suitable.

but looks like that using adDate is actually truncating the field value

Using DBGO I get DateTime fields as ftDateTime, even if the field holds only date value (i.e. an integer)
So I think it is a problem of how ZEOS declares or handles DateTime fields.

[KDeveloper2]

Hi,
any news here?

i had the same problem with Delphi 2010.
i connect to Access with zeos, and the Date/Time Fields
are only available as Date Fields.


when i change ZDbcAdoUtils

Code: Select all

function ConvertAdoToSqlType(FieldType: SmallInt): TZSQLType;
begin
  case FieldType of
  ...
    adDate : Result := stTimestamp;
  ...

then i will displayed correctly in runtime, but not in designtime

[glk70]

Unfortunately No News.

as far as I could debug the problem there is something worng with the interface with ADO.
but I could not manage to lock down tot the problem.

It's likely a bit mismatch in field definition. So ADO returns only date fields because it is instructed in doing so by Zeos. But it is only a suspect.

[KDeveloper2]

I think there is also another problem with access,
because there is only Date/Time, no Date and no Time and Delphi 2010 need the exact field definition.

I think you have to save your time value in a seperate string field.

[glk70]

Access saves Date/Time , Date and Time values in the same fieldType.
I've not a clear detail how it can recognize the three different styles

What I knwo for sure is that

01-01-1980 15:38
is different from
15:38

but other db Interfaces (for example DBGO ) holds the date/teim , date and time values correctly

So I have to say there is something wrong in Zeos

Saving date and time values in two separate fields is a workaround.
Anyway be aware that I tried this

INSERTO INTO someTable ( timeField ) VALUES ( "15:38" )
SELECT timeField FROM someTable

and I got 0 ( zero ) !
So be carefull chosing the correct field type when saving times.

[cañones]

I'm using a MSaccess database thorugh ADO interface via Zeosib

Executing a query with DastTime fields I always get only the date (integer) portion of the field.
I've followed the problem as far as I could and I've seend that the field type returned is a STdate, which is converted into a TDate.
TDate and TDateTime are just the same type in Delphi, yet the contet of the field is always an integer. S only the date portion of the field is returned.

I obviously need the time portion also.
Looks like the ADO provider is returning only the date portion of the field.

Any suggestion ?

hello, could you solve this problem, I feel the same.
work with delphi 7 and Zeos 6.6.5.
Sorry for my english

[glk70]

hello, could you solve this problem, I feel the same.
work with delphi 7 and Zeos 6.6.5.
Sorry for my english

Sorry I cannot use Delphi7 because it is incompatible with Windows7.
The only version I can use is Delphi2010.
And Zeos Lib seems to be unable to handle correctly the datetime conversion for MsAccess