[ADO.Net, Access 97] how to determine the exact type of DateTime column

[scj]

Hi all,

I need to determine the exact type of DateTime column(*) in an Access 97
database.

I'm able to do this with an Access 2 database.
With ADO.Net and VB.Net, I do something like this :

Dim oDT As New DataTable()
Dim oCnx As OleDbConnection
Dim oGUID As New OleDbSchemaGuid()
....
oCnx = New OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=xxx.mdb;Persist Security Info=False" )
oCnx.Open()
....
oDT = oCnx.GetOleDbSchemaTable( oGUID.Columns, New Object() { Nothing,
Nothing, "TABLE_NAME" } )
....

Then, the column :
- "DATA_TYPE" gives the type
- "COLUMN_FLAGS" gives me more information

With Access 2, "DATA_TYPE" is "7" for a date and "COLUMN_FLAGS" is "90" for
a DateTime or something else for just a Date.

The problem with Access 97 is that the "COLUMN_FLAGS" is the same with both
Date and DateTime.

I tryed to use system tables, with the hope to find a table with table
structure and excat column type, but without succes.

Thanks for all assistance,

Marc

(*) A DateTime can store date, time, datetime, etc...

 

[Dirk Goldgar]

Hi all,

I need to determine the exact type of DateTime column(*) in an Access
97 database.

I'm able to do this with an Access 2 database.
With ADO.Net and VB.Net, I do something like this :

Dim oDT As New DataTable()
Dim oCnx As OleDbConnection
Dim oGUID As New OleDbSchemaGuid()
...
oCnx = New OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=xxx.mdb;Persist Security Info=False" )
oCnx.Open()
...
oDT = oCnx.GetOleDbSchemaTable( oGUID.Columns, New Object() { Nothing,
Nothing, "TABLE_NAME" } )
...

Then, the column :
- "DATA_TYPE" gives the type
- "COLUMN_FLAGS" gives me more information

With Access 2, "DATA_TYPE" is "7" for a date and "COLUMN_FLAGS" is
"90" for a DateTime or something else for just a Date.

The problem with Access 97 is that the "COLUMN_FLAGS" is the same
with both Date and DateTime.

I tryed to use system tables, with the hope to find a table with table
structure and excat column type, but without succes.

Thanks for all assistance,

Marc

(*) A DateTime can store date, time, datetime, etc...

I'm not familiar with Access 2. Did it have different field types for
Date and DateTime? In Access 97, there's only one type of field for
dates, times, and datetimes, so I don't see how you could distinguish
them by checking column properties.

 

[scj]

For end-user, Access 2 and Access 97 are very similar about dates, times and
datetimes : with the graphic interface, you select the column type and then
you are able to give more precision : you can store just date, just time, or
datetime... I suppose the "physical storage" is the same and there is
probably just something like a CHECK constraint ?

So, with Access 2 and Access 97, there is just one type for dates, times,
and datetimes... *but* with both you can give more "storage information"
with the ability to indicate you just want date, or time, or datetime...

And my problem is to have / write a piece of code giving me this
information (via ADO.Net) : date, time, or datetime ?

 

[Paul Overway]

Date/Time fields in a Jet database are always stored as a double....no
matter how it is displayed. This has been the case since version 1. I
think you are confused between storage and display.

 

[Dirk Goldgar]

For end-user, Access 2 and Access 97 are very similar about dates,
times and datetimes : with the graphic interface, you select the
column type and then you are able to give more precision : you can
store just date, just time, or datetime... I suppose the "physical
storage" is the same and there is probably just something like a
CHECK constraint ?

So, with Access 2 and Access 97, there is just one type for dates,
times, and datetimes... *but* with both you can give more "storage
information" with the ability to indicate you just want date, or
time, or datetime...

Although I don't know about Access 2, I can say that with Access 97 this
is simply not true. All date fields have the same format, and can store
both date and time. You *can* do any of the following:

1. set a field's Format property to indicate how the field is to be
displayed (by default)

2. set a field's InputMask property to force entry in a particular
format; for example, "mm/dd/yyyy"

3. set a Validation Rule (such as "[DateField]=Int([DateField])") to
require that entries contain no fractional (i.e., time) portion.

Option 3 is the *only* way you can enforce a dates-only restriction at
the table level, as it's the equivalent of a CHECK constraint. The
others will both allow records containing date+time data to be inserted
via SQL or other means.

The trouble is, there's no one thing you can check about a column's
properties that will tell you if any of these properties has been set.
You could presumably use DAO to examine the properties of the fields in
a TableDef, but you'd still have to do a fair amount of interpretation
to make your best guess as to whether the field is intended to hold
date, time, or date+time values. I don't know about ADO.Net, but I
doubt it gives enough information. Possibly it could make the
information available as extended properties, but I simply have no
information on the subject.

And my problem is to have / write a piece of code giving me this
information (via ADO.Net) : date, time, or datetime ?

One thing you could conceivably do is look at actual values from the
table to see if they contain only times, or only dates, or date+time.
Another thing you could do is try to save a record with a field value
containing the elements you want, and see if an error is raised. That
at least would tell you whether there's a Validation Rule in effect.

 

[Douglas J. Steele]

Access 2 didn't actually have a Date data type: you needed to use a variant
(which is what Data type 7 corresponds to).

As far as I can remember, though, there was no option to indicate Date only
or Time only, as you seem to think there was.

The comments Dirk has made were true for Access 2 as well.

 

[scj]

No confusion, I wrote :
1) I suppose the "physical storage" is the same
2) there is probably just something like a CHECK constraint

--> As you say : Jet stores Date/Time as a double ! You confirm my first
assumption

You can configure Jet in order to store just a date, or just a time (for
example) in a Date/Time field. The choice you made (just store time, for
example) seems to work approximately like a CHECK CONSTRAINT. In short you
can't insert a row with a date/time in a column which just accept date, or
time, for example... It's not only a display question !

But with this discussion I don't have a solution to get the exact data type
which is really stored in a Date/Time field ! ADO.Net and the
GetOleDbSchemaTable don't give me enough informations... :-(

 

[scj]

Although I don't know about Access 2, I can say that with Access 97 this

is simply not true. All date fields have the same format, and can store
both date and time.

I just speak a bit of english, but it seems to me that this phrase explains
exactly the same things than yours :

same format == just one type (in my poor english)

And I disagree with your phrase "You *can* do any of the following"...
Actually you *must* do one of the "following" because of the default choice
for the field's format ;-)

One thing you could conceivably do is look at actual values from the
table to see if they contain only times, or only dates, or date+time.

I can't always do this because sometimes the table is empty...

Another thing you could do is try to save a record with a field value
containing the elements you want, and see if an error is raised. That
at least would tell you whether there's a Validation Rule in effect.

it's not so easy because of things like foreign key and other constraint's
column...

For end-user, Access 2 and Access 97 are very similar about dates,
times and datetimes : with the graphic interface, you select the
column type and then you are able to give more precision : you can
store just date, just time, or datetime... I suppose the "physical
storage" is the same and there is probably just something like a
CHECK constraint ?

So, with Access 2 and Access 97, there is just one type for dates,
times, and datetimes... *but* with both you can give more "storage
information" with the ability to indicate you just want date, or
time, or datetime...

Although I don't know about Access 2, I can say that with Access 97 this
is simply not true. All date fields have the same format, and can store
both date and time. You *can* do any of the following:

1. set a field's Format property to indicate how the field is to be
displayed (by default)

2. set a field's InputMask property to force entry in a particular
format; for example, "mm/dd/yyyy"

3. set a Validation Rule (such as "[DateField]=Int([DateField])") to
require that entries contain no fractional (i.e., time) portion.

Option 3 is the *only* way you can enforce a dates-only restriction at
the table level, as it's the equivalent of a CHECK constraint. The
others will both allow records containing date+time data to be inserted
via SQL or other means.

The trouble is, there's no one thing you can check about a column's
properties that will tell you if any of these properties has been set.
You could presumably use DAO to examine the properties of the fields in
a TableDef, but you'd still have to do a fair amount of interpretation
to make your best guess as to whether the field is intended to hold
date, time, or date+time values. I don't know about ADO.Net, but I
doubt it gives enough information. Possibly it could make the
information available as extended properties, but I simply have no
information on the subject.

And my problem is to have / write a piece of code giving me this
information (via ADO.Net) : date, time, or datetime ?

One thing you could conceivably do is look at actual values from the
table to see if they contain only times, or only dates, or date+time.
Another thing you could do is try to save a record with a field value
containing the elements you want, and see if an error is raised. That
at least would tell you whether there's a Validation Rule in effect.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Brendan Reynolds \(MVP\)]

A JET date/time field always stores both a date and a time, although Access
may not display them. The value is stored as a Double, with the part before
the decimal point indicating the number of days since 30 December, 1899, and
the part after the decimal point indicating the time as a fraction of a day,
e.g. .5 is noon, .25 is 6AM, .75 is 6PM. Either part may be 0, but that does
not indicate the absence of a date or of a time, 0 is a valid date (30
December 1899) and .0 is a valid time (midnight). You can not distinguish
between 'different types of date/time fields' by looking at the schema of
the table, because there are no different types.

If you want to prove this for yourself, try creating a table with two
date/time fields. Set the format for one field to long date, and for the
other to long time. Enter a few test values. Enter a time with no date in
the field with the long date format, and a date with no time in the field
with the long time format, ignoring the way they are displayed for now. Now
go into design view and swap the formats around - give the field that has
the long date format the long time format, and the field that has the long
time format the long date format. Go back into datasheet view and look at
the values you previously entered.

Even if your code were to look at the actual values in the table, to be
completely sure, you would need to look at every record. It would not be
safe to just look at the first record, find that the date was 0 (30
December, 1899) and assume that therefore the field was being used only to
record times - just because no date part was explicitly entered in the first
record does not mean that no date part was explicitly entered in subsequent
records.

What is the problem you're trying to solve here? If we knew what the problem
was, perhaps we could suggest an alternative solution?

--
Brendan Reynolds (MVP)
(e-mail address removed)

Although I don't know about Access 2, I can say that with Access 97 this
is simply not true. All date fields have the same format, and can store
both date and time.

I just speak a bit of english, but it seems to me that this phrase explains
exactly the same things than yours :

same format == just one type (in my poor english)

And I disagree with your phrase "You *can* do any of the following"...
Actually you *must* do one of the "following" because of the default choice
for the field's format ;-)

One thing you could conceivably do is look at actual values from the
table to see if they contain only times, or only dates, or date+time.

I can't always do this because sometimes the table is empty...

Another thing you could do is try to save a record with a field value
containing the elements you want, and see if an error is raised. That
at least would tell you whether there's a Validation Rule in effect.

it's not so easy because of things like foreign key and other constraint's
column...

For end-user, Access 2 and Access 97 are very similar about dates,
times and datetimes : with the graphic interface, you select the
column type and then you are able to give more precision : you can
store just date, just time, or datetime... I suppose the "physical
storage" is the same and there is probably just something like a
CHECK constraint ?

So, with Access 2 and Access 97, there is just one type for dates,
times, and datetimes... *but* with both you can give more "storage
information" with the ability to indicate you just want date, or
time, or datetime...

Although I don't know about Access 2, I can say that with Access 97 this
is simply not true. All date fields have the same format, and can store
both date and time. You *can* do any of the following:

1. set a field's Format property to indicate how the field is to be
displayed (by default)

2. set a field's InputMask property to force entry in a particular
format; for example, "mm/dd/yyyy"

3. set a Validation Rule (such as "[DateField]=Int([DateField])") to
require that entries contain no fractional (i.e., time) portion.

Option 3 is the *only* way you can enforce a dates-only restriction at
the table level, as it's the equivalent of a CHECK constraint. The
others will both allow records containing date+time data to be inserted
via SQL or other means.

The trouble is, there's no one thing you can check about a column's
properties that will tell you if any of these properties has been set.
You could presumably use DAO to examine the properties of the fields in
a TableDef, but you'd still have to do a fair amount of interpretation
to make your best guess as to whether the field is intended to hold
date, time, or date+time values. I don't know about ADO.Net, but I
doubt it gives enough information. Possibly it could make the
information available as extended properties, but I simply have no
information on the subject.

And my problem is to have / write a piece of code giving me this
information (via ADO.Net) : date, time, or datetime ?

One thing you could conceivably do is look at actual values from the
table to see if they contain only times, or only dates, or date+time.
Another thing you could do is try to save a record with a field value
containing the elements you want, and see if an error is raised. That
at least would tell you whether there's a Validation Rule in effect.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[scj]

The small enterprise that hired me works with databases such as Access,
MSDE, Oracle, DB2, Interbase/Firebird, etc... Access is/was the default
database. But sometimes the customers who used our softwares aren't
satisfied with Access... In order to make things easier, we decided to use
only two major field's types : CHAR and [tiny,small]int or decimal. Thus a
boolean became a bit (MSDE) or number(1) (Oracle) with 0 for "false" and 1
for "true". A date is stored with a char(8) ("YYYYMMDD"), a time with a
char(6) ("HHMMSS") and a date-time with a char(15) ("YYYYMMDD HHMMSS")...
It's easier for us... The problem is that some old customers with old
databases such as Access 2 (or 97) decide to use new versions of ours
softwares... This new versions don't work with date-time fields, or
boolean... And as soon as possible we decided to replace Access with MSDE,
and VB3...6 with VB.Net or C# ! We decided to offer our customers a small
utility that convert(*) an Access datable in a MSDE database (and later
Oracle,...). It works fine with old Access 2 databases ! Sadly I've just a
problem with Access 97 and date-time fields... I don't know how I can do
with ADO.Net to know if a Date-time field contains really date-time, or
date, or time...

I hope you understand me, even if my english is very poor...

(*) it creates tables with primary keys, and then it makes a copy of the
datas (with boolean and date-time conversions as explained)

A JET date/time field always stores both a date and a time, although Access
may not display them. The value is stored as a Double, with the part before
the decimal point indicating the number of days since 30 December, 1899, and
the part after the decimal point indicating the time as a fraction of a day,
e.g. .5 is noon, .25 is 6AM, .75 is 6PM. Either part may be 0, but that does
not indicate the absence of a date or of a time, 0 is a valid date (30
December 1899) and .0 is a valid time (midnight). You can not distinguish
between 'different types of date/time fields' by looking at the schema of
the table, because there are no different types.

If you want to prove this for yourself, try creating a table with two
date/time fields. Set the format for one field to long date, and for the
other to long time. Enter a few test values. Enter a time with no date in
the field with the long date format, and a date with no time in the field
with the long time format, ignoring the way they are displayed for now. Now
go into design view and swap the formats around - give the field that has
the long date format the long time format, and the field that has the long
time format the long date format. Go back into datasheet view and look at
the values you previously entered.

Even if your code were to look at the actual values in the table, to be
completely sure, you would need to look at every record. It would not be
safe to just look at the first record, find that the date was 0 (30
December, 1899) and assume that therefore the field was being used only to
record times - just because no date part was explicitly entered in the first
record does not mean that no date part was explicitly entered in subsequent
records.

What is the problem you're trying to solve here? If we knew what the problem
was, perhaps we could suggest an alternative solution?

--
Brendan Reynolds (MVP)
(e-mail address removed)

Although I don't know about Access 2, I can say that with Access 97 this
is simply not true. All date fields have the same format, and can store
both date and time.

I just speak a bit of english, but it seems to me that this phrase explains
exactly the same things than yours :

with Access 2 and Access 97, there is just one type for dates,
times, and datetimes

same format == just one type (in my poor english)

And I disagree with your phrase "You *can* do any of the following"...
Actually you *must* do one of the "following" because of the default choice
for the field's format ;-)

One thing you could conceivably do is look at actual values from the
table to see if they contain only times, or only dates, or date+time.

I can't always do this because sometimes the table is empty...

Another thing you could do is try to save a record with a field value
containing the elements you want, and see if an error is raised. That
at least would tell you whether there's a Validation Rule in effect.

it's not so easy because of things like foreign key and other constraint's
column...

For end-user, Access 2 and Access 97 are very similar about dates,
times and datetimes : with the graphic interface, you select the
column type and then you are able to give more precision : you can
store just date, just time, or datetime... I suppose the "physical
storage" is the same and there is probably just something like a
CHECK constraint ?

So, with Access 2 and Access 97, there is just one type for dates,
times, and datetimes... *but* with both you can give more "storage
information" with the ability to indicate you just want date, or
time, or datetime...

Although I don't know about Access 2, I can say that with Access 97 this
is simply not true. All date fields have the same format, and can store
both date and time. You *can* do any of the following:

1. set a field's Format property to indicate how the field is to be
displayed (by default)

2. set a field's InputMask property to force entry in a particular
format; for example, "mm/dd/yyyy"

3. set a Validation Rule (such as "[DateField]=Int([DateField])") to
require that entries contain no fractional (i.e., time) portion.

Option 3 is the *only* way you can enforce a dates-only restriction at
the table level, as it's the equivalent of a CHECK constraint. The
others will both allow records containing date+time data to be inserted
via SQL or other means.

The trouble is, there's no one thing you can check about a column's
properties that will tell you if any of these properties has been set.
You could presumably use DAO to examine the properties of the fields in
a TableDef, but you'd still have to do a fair amount of interpretation
to make your best guess as to whether the field is intended to hold
date, time, or date+time values. I don't know about ADO.Net, but I
doubt it gives enough information. Possibly it could make the
information available as extended properties, but I simply have no
information on the subject.

And my problem is to have / write a piece of code giving me this
information (via ADO.Net) : date, time, or datetime ?

One thing you could conceivably do is look at actual values from the
table to see if they contain only times, or only dates, or date+time.
Another thing you could do is try to save a record with a field value
containing the elements you want, and see if an error is raised. That
at least would tell you whether there's a Validation Rule in effect.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Paul Overway]

Remember, you are really working with a double. If there is a fractional
part to the value, time is indicated. Otherwise, it is just a date....with
the default time being midnight. If time is indicated...there will always
be a date as well. If the field is datetime data type, there is no such
thing as time only....it ALWAYS includes a date.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

The small enterprise that hired me works with databases such as Access,
MSDE, Oracle, DB2, Interbase/Firebird, etc... Access is/was the default
database. But sometimes the customers who used our softwares aren't
satisfied with Access... In order to make things easier, we decided to use
only two major field's types : CHAR and [tiny,small]int or decimal. Thus a
boolean became a bit (MSDE) or number(1) (Oracle) with 0 for "false" and 1
for "true". A date is stored with a char(8) ("YYYYMMDD"), a time with a
char(6) ("HHMMSS") and a date-time with a char(15) ("YYYYMMDD HHMMSS")...
It's easier for us... The problem is that some old customers with old
databases such as Access 2 (or 97) decide to use new versions of ours
softwares... This new versions don't work with date-time fields, or
boolean... And as soon as possible we decided to replace Access with MSDE,
and VB3...6 with VB.Net or C# ! We decided to offer our customers a small
utility that convert(*) an Access datable in a MSDE database (and later
Oracle,...). It works fine with old Access 2 databases ! Sadly I've just a
problem with Access 97 and date-time fields... I don't know how I can do
with ADO.Net to know if a Date-time field contains really date-time, or
date, or time...

I hope you understand me, even if my english is very poor...

(*) it creates tables with primary keys, and then it makes a copy of the
datas (with boolean and date-time conversions as explained)

A JET date/time field always stores both a date and a time, although Access
may not display them. The value is stored as a Double, with the part before
the decimal point indicating the number of days since 30 December, 1899, and
the part after the decimal point indicating the time as a fraction of a day,
e.g. .5 is noon, .25 is 6AM, .75 is 6PM. Either part may be 0, but that does
not indicate the absence of a date or of a time, 0 is a valid date (30
December 1899) and .0 is a valid time (midnight). You can not distinguish
between 'different types of date/time fields' by looking at the schema of
the table, because there are no different types.

If you want to prove this for yourself, try creating a table with two
date/time fields. Set the format for one field to long date, and for the
other to long time. Enter a few test values. Enter a time with no date in
the field with the long date format, and a date with no time in the field
with the long time format, ignoring the way they are displayed for now. Now
go into design view and swap the formats around - give the field that has
the long date format the long time format, and the field that has the long
time format the long date format. Go back into datasheet view and look at
the values you previously entered.

Even if your code were to look at the actual values in the table, to be
completely sure, you would need to look at every record. It would not be
safe to just look at the first record, find that the date was 0 (30
December, 1899) and assume that therefore the field was being used only to
record times - just because no date part was explicitly entered in the first
record does not mean that no date part was explicitly entered in subsequent
records.

What is the problem you're trying to solve here? If we knew what the problem
was, perhaps we could suggest an alternative solution?

"[DateField]=Int([DateField])")

to

 

[Brendan Reynolds \(MVP\)]

If you don't know in advance whether the field you're converting will be
used for date/time, date only, or time only information, then I think the
only safe option is to examine the actual values stored in the field. I'm
thinking that rather than stepping through individual records, it might be
possible to do it via SQL. The following SQL statements are examples of how
I might do it in Access - you may need to modify them somewhat for your
environment, but hopefully they may at least serve to get you started.

If no date part has been explicitly entered in any record, the date part
will default to 30 December, 1899. So, if the following JET SQL statement
returns a value greater than zero, then at least one date part has been
explicitly entered in the field ...

SELECT Count(*) AS TheCount
FROM Table1
WHERE (((Table1.TestDate)<#30 Dec 1899# Or (Table1.TestDate)>=#31 Dec
1899#));

Similarly, if the following JET SQL statement returns a value greater than
zero, then at least one time part has been explicitly entered in the field
....

SELECT Count(*) AS TheCount
FROM Table1
WHERE (((Hour([TestDate]))>0)) OR (((Minute([TestDate]))>0)) OR
(((Second([TestDate]))>0));

That leaves us with the problem of fields in which no values have been
entered in any record. At the moment, I can think of only two options to
deal with that situation: 1) prompt the user to choose, or 2) default to
char(15), as that can hold any of the possible values.

--
Brendan Reynolds (MVP)
(e-mail address removed)

The small enterprise that hired me works with databases such as Access,
MSDE, Oracle, DB2, Interbase/Firebird, etc... Access is/was the default
database. But sometimes the customers who used our softwares aren't
satisfied with Access... In order to make things easier, we decided to use
only two major field's types : CHAR and [tiny,small]int or decimal. Thus a
boolean became a bit (MSDE) or number(1) (Oracle) with 0 for "false" and 1
for "true". A date is stored with a char(8) ("YYYYMMDD"), a time with a
char(6) ("HHMMSS") and a date-time with a char(15) ("YYYYMMDD HHMMSS")...
It's easier for us... The problem is that some old customers with old
databases such as Access 2 (or 97) decide to use new versions of ours
softwares... This new versions don't work with date-time fields, or
boolean... And as soon as possible we decided to replace Access with MSDE,
and VB3...6 with VB.Net or C# ! We decided to offer our customers a small
utility that convert(*) an Access datable in a MSDE database (and later
Oracle,...). It works fine with old Access 2 databases ! Sadly I've just a
problem with Access 97 and date-time fields... I don't know how I can do
with ADO.Net to know if a Date-time field contains really date-time, or
date, or time...

I hope you understand me, even if my english is very poor...

(*) it creates tables with primary keys, and then it makes a copy of the
datas (with boolean and date-time conversions as explained)

A JET date/time field always stores both a date and a time, although Access
may not display them. The value is stored as a Double, with the part before
the decimal point indicating the number of days since 30 December, 1899, and
the part after the decimal point indicating the time as a fraction of a day,
e.g. .5 is noon, .25 is 6AM, .75 is 6PM. Either part may be 0, but that does
not indicate the absence of a date or of a time, 0 is a valid date (30
December 1899) and .0 is a valid time (midnight). You can not distinguish
between 'different types of date/time fields' by looking at the schema of
the table, because there are no different types.

If you want to prove this for yourself, try creating a table with two
date/time fields. Set the format for one field to long date, and for the
other to long time. Enter a few test values. Enter a time with no date in
the field with the long date format, and a date with no time in the field
with the long time format, ignoring the way they are displayed for now. Now
go into design view and swap the formats around - give the field that has
the long date format the long time format, and the field that has the long
time format the long date format. Go back into datasheet view and look at
the values you previously entered.

Even if your code were to look at the actual values in the table, to be
completely sure, you would need to look at every record. It would not be
safe to just look at the first record, find that the date was 0 (30
December, 1899) and assume that therefore the field was being used only to
record times - just because no date part was explicitly entered in the first
record does not mean that no date part was explicitly entered in subsequent
records.

What is the problem you're trying to solve here? If we knew what the problem
was, perhaps we could suggest an alternative solution?

"[DateField]=Int([DateField])")

to