Milliseconds in Date/Time in MS Access

[maneeshkhare]

I came across a problem in my application. We wanted to be able to
sort Date/Time, whose entries included milliseconds. However, MS
Access does not allow ms in Date/Time entry. So this is what I did
(just thought somebody might find this handy)

1. From .NET, whatever DateTime var you have, get its 'Ticks'
<datetimevar.Ticks> This is a long integer telling you the number of
'ticks' since epoch (a ref point).

2. Since MS Access will not support the length of such a long integer
(long in MS Access is only 1 Billion +ve and -ve). So divide it by
something like 1000000000.0000 to get a double value in your program,
and feed this to the mdb database, in place of the Date/Time. Now you
have something that gives the exact precision of your time in .NET
program.

3. Make sure your table supports double in this field of mdb, with
appropriate places of decimal.

4. Now you can have the table with as much precision for the Date/Time
as you want.

regards,
Maneesh

 

[maneeshkhare]

It won't be 'readable' physically, but it works for
1. Sorting the table by this field, reflecting the precision of
DataTime u need
2. Reading and reconverting back to DateTime var in .NET code, as long
as u remember to multiply with whatever power of 10 you divided by at
the time of storing.

 

[peregenem]

We wanted to be able to
sort Date/Time, whose entries included milliseconds. However, MS
Access does not allow ms in Date/Time entry.

Like all SQLs, are modelled using start_date and end_date columns of
type DATETIME. In Access/Jet, the DATETIME is stored as a double float
under the covers. However all the temporal functions (DATEADD,
DATEDIFF, etc) all use whole seconds as the smallest value. This means
that, although you can store values of greater accuracy than one second
in a DATETIME column, you can't do anything useful with them.

However, you are modelling 'ticks' which would appear to be outside the
DATETIME domain.

Since MS Access will not support the length of such a long integer
(long in MS Access is only 1 Billion +ve and -ve). So divide it by
something like 1000000000.0000 to get a double value in your program

I would never recommend using an inexact data type such as FLOAT
(Double) to store exact values.

Jet/Access has a DECIMAL data type, being a scaled integer of up 28
digits e.g.

CREATE TABLE Test (ticks DECIMAL(28,0))
;
INSERT INTO Test VALUES (9999999999999999999999999999)
;

If that range of values isn't large enough, you can 'wrap' and use the
negative values too:

INSERT INTO Test VALUES (-9999999999999999999999999999)
;

Is this enough for your needs?