TimeSpan Data elements

[Dave]

I am using an Access Database (Access 2002) with a program written in
VB.NET using ADO.NET. I want to compute a timespan element and store
it in the database to be displayed as
<days>.<hours>:<minutes>:<seconds>. What's the best way to do this?

Things I've tried and why they don't work.
1. Storing the timespan element as a Date/Time column in the database,
using the DBTime database type in the data queries. Doesn't work
because VB throws a InvalidCastException trying to cast a TimeSpan to a
DateTime.
2. Storing the timespan element as a Date/Time column in the database,
using the DBTimeSamp database type, and converting TimeSpan to a
DateTime by doing a dtTimeZero.Add(timespan) operation (where
dtTimeZero is a DateTime type with value 0). Doesn't work because VB
calls dtTimeZero as "#1/1/0001 hh:mm:ss#", whereas Access stores it in
the database as "#1/1/2001 hh:mm:ss#", so update queries don't match.
3. Storing the two events in the database as Date/Time columns, and
constructing a query to take the difference between them. Doesn't work
because the difference is a real number, and Access won't let me format
it into a time format. So I get difference values like 0.6901357
instead of 0.14:31:15 (or whatever)

Any help would be appreciated. Thanks.

 

[Douglas J. Steele]

As you've discovered, the Date/Time data type is intended to be used as a
timestamp: a specific point in time, not a duration.

I would recommend storing the timespan as a Long Integer as total seconds,
and writing a function to format it as required.

 

[Jamie Collins]

I am using an Access Database (Access 2002) with a program written in
VB.NET using ADO.NET. I want to compute a timespan element and store
it in the database to be displayed as
<days>.<hours>:<minutes>:<seconds>.

Sounds like you want the ANSI SQL-92 day-time INTERVAL type.
Unfortunately, the only temporal data type in Access/Jet is DATETIME
(similar to SQL-92's TIMESTAMP type).

The usual workaround is to use separate columns for value (INTEGER) and
granularity (text to match the characters used in the interval
constructor functions DATEDIFF and DATEADD).

Jamie.

--

 

[Dave]

Thanks to both of you for your responses.