Beta TPS ODBC (long) Date conversion ? 
Author Message
 Beta TPS ODBC (long) Date conversion ?

I tried to use tps beta-2 odbc with MS Query. Tps long-date field is number
in MS Query and Not Date ? Big Probelm?.

I had come to know that C4 will have answer for this but what will happen
to dos tps user. They will never be able to use their data  under ODBC? Can
Some one in Topspeed can answer this issue? Use C4 or forget tps ODBC, is a
policy ?

Kesh Patel
Clarion Fen Since 1990.



Wed, 24 Nov 1999 03:00:00 GMT  
 Beta TPS ODBC (long) Date conversion ?

Quote:

>I tried to use tps beta-2 odbc with MS Query. Tps long-date field is number
>in MS Query and Not Date ? Big Probelm?.
>I had come to know that C4 will have answer for this but what will happen
>to dos tps user. They will never be able to use their data  under ODBC? Can
>Some one in Topspeed can answer this issue? Use C4 or forget tps ODBC, is a
>policy ?

From the Help file (TPSODBC.HLP),

To use a field stored as a LONG using Clarion Standard Dates or
Clarion Standard Times, you should create a formula in the product you
are using to access the TopSpeed files. Here are some examples:

Converting a Clarion date to a Microsoft Excel Date: Subtract 36161
from the Clarion date value.

Converting a Microsoft Excel date to a Clarion Date: Add 36161 to the
Microsoft Excel date value.

Converting a Clarion time to a Microsoft Excel time: Divide the
Clarion time value by 86400 (the number of seconds in one day), then
divide by 100 to express as a decimal value.

Converting a Microsoft Excel time to a Clarion time: Multiply the
Microsoft Excel time value by 0.0000001157407407407410 (1 divided by
the number of seconds in a day; includes the adjustment to convert
back from a percentage).

BTW: If anyone has examples of formulas to use in other products,
please send them to me or post here. I will include the examples in
future docs.

HTH,
Jim

+============================================================>
| Jim DeFabia         TopSpeed Corporation-Documentation

| Standard Disclaimer: These opinions are mine & mine alone.
+============================================================>



Wed, 24 Nov 1999 03:00:00 GMT  
 Beta TPS ODBC (long) Date conversion ?



Quote:
>I tried to use tps beta-2 odbc with MS Query. Tps long-date field is number
>in MS Query and Not Date ? Big Probelm?.

>I had come to know that C4 will have answer for this but what will happen
>to dos tps user. They will never be able to use their data  under ODBC? Can
>Some one in Topspeed can answer this issue? Use C4 or forget tps ODBC, is a
>policy ?

I have been thinking about the problem. The trouble is that the field
in the TPS file is just a LONG - there is no hint that it is anything
else, since picture information for LONG's is not stored in the TPS
file.

One solution I am considering is to have a convention based on the
field name - if a LONG field name contains "DATE" say, then it would
be assumed that the field should be interpreted as a date value.

The string which triggers this behavior could be configured in the
data source dialog. Similarly for time fields.

While this solution is a bit of a "kludge" I think it might be the
most practical way forward. I would be interested to hear from anyone
whether this solution would be practical for them, or if they can
think of any other solutions to the problem.

George



Thu, 25 Nov 1999 03:00:00 GMT  
 Beta TPS ODBC (long) Date conversion ?

Is this possible???:
In the data source dialog, add a list for fields that need special driver
action. Let the user add entries (table.field) to this list by picking
field names from a list of reported available field names. Together with
each picked field name, save "special action attributes". The driver should
then interpret fields on this lists according to these attributes.
Examples of "special action attributes":
- Interpret as date value
- Interpret as time value
- Put reported field name in double quotes

As indicated this could also solve the problem of field names that are
reserved words in ODBC/SQL. There are probably a lot of other useful
attributes that could have been added, but this is what I can come up with
at the moment.

Geir Hansen
Nordic Innovator AS

Quote:

> One solution I am considering is to have a convention based on the
> field name - if a LONG field name contains "DATE" say, then it would
> be assumed that the field should be interpreted as a date value.

> The string which triggers this behavior could be configured in the
> data source dialog. Similarly for time fields.

> While this solution is a bit of a "kludge" I think it might be the
> most practical way forward. I would be interested to hear from anyone
> whether this solution would be practical for them, or if they can
> think of any other solutions to the problem.

> George



Thu, 25 Nov 1999 03:00:00 GMT  
 Beta TPS ODBC (long) Date conversion ?

George,

I just testet more with a field named SUM. I can't get the query to work
even if I use double
quotes. Maybe you need to either automatically report/use a substitue name
for such field names, or let the user in the data source dialog enter
substitute names for such fields.

Geir Hansen
Nordic Innovator AS

Quote:

> .....
> - Put reported field name in double quotes

> As indicated this could also solve the problem of field names that are
> reserved words in ODBC/SQL.



Fri, 26 Nov 1999 03:00:00 GMT  
 Beta TPS ODBC (long) Date conversion ?



Quote:
>I just testet more with a field named SUM. I can't get the query to work
>even if I use double quotes.

Geir,
I think this is specific to the front-end program you are using - when
entering raw SQL using say the ODBC test program,
    SELECT "SUM" FROM TEST
seems to work OK.
George


Sat, 27 Nov 1999 03:00:00 GMT  
 Beta TPS ODBC (long) Date conversion ?

George,

The front end is Microsoft Query from Office 97. Not a frontend I think you
should ignore!

Geir.

Quote:

> I think this is specific to the front-end program you are using - when
> entering raw SQL using say the ODBC test program,
>     SELECT "SUM" FROM TEST
> seems to work OK.



Sat, 27 Nov 1999 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. DATE vs LONG in .tps files and ODBC

2. TPS ODBC Beta Driver Not Working With ASP

3. BETA ODBC for *.tps Question

4. TPS ODBC Date Problem

5. in ASP displaying date from TPS files stored as Long

6. TPS ODBC and Date Fields in Crystal Reports?????

7. Conversion of MDB to TPS - odbc error

8. problem with empty date via Topspeed ODBC beta 5

9. TPS file recovery utility/TPS ODBC problem

10. Connect to TSM ODBC Driver with Regina and Rexx/SQL for ODBC no longer possible

11. Clarion Date (Long) -> Access Date\Time

12. Date Conversion problem: Date == 2079/12/31

 

 
Powered by phpBB® Forum Software