Date as long integer
Author Message
Date as long integer

Dear All

I've long felt that using a date/time value for just a date is not a good
idea: particularly if using it for a primary key. Double precision floats
just don't make for good equality comparisons.

The problem: 12345.9999999999 <> 12346.000000

So I experimented with long integers. This seems to work: field format set
to "Short Date" works fine, so does setting Default to =Date().

So the question is, what are the problems with this approach. Are there
any? Should we all be using this? Are you all using this already?

B Wishes

Tim F

Sun, 12 Dec 2004 03:25:06 GMT
Date as long integer
I guess if you want to rewrite all the built in date
functionality ... I never had a problem comparing dates
when defined as a date.  Please write an example of how
you will use your long integer to calculate the number of
days prior to today's date to demonstrate the superiority
of this claim. Also calculate the third monday of the
month following any given date.  If the given date is past
the third monday of a month the correct result is the
third monday of the following month.

I just don't see how doing these tasks with long integers

Quote:
>-----Original Message-----
>Dear All

>I've long felt that using a date/time value for just a
date is not a good
>idea: particularly if using it for a primary key. Double
precision floats
>just don't make for good equality comparisons.

>The problem: 12345.9999999999 <> 12346.000000

>So I experimented with long integers. This seems to work:
field format set
>to "Short Date" works fine, so does setting Default to
=Date().

>So the question is, what are the problems with this

approach. Are there

- Show quoted text -

Quote:
>any? Should we all be using this? Are you all using this

>B Wishes

>Tim F

>.

Sun, 12 Dec 2004 03:41:45 GMT
Date as long integer
I'm a bit puzzled. Exactly what are you trying to do? If you are trying to
use a date as a primary key that is NOT a good idea because you might have
more than one record entered on the same day and, if the field is the
primary key, access won't allow that. Also, if you are just wanting to use
long integer as a date field, then you lose the functionality of the time
portion of the date. The numbers to the right of the decimal place in a
double precision float date field is calculated as the time of the day.

--

Lynn Trapp
MS Access MVP

www.nwidevelopers.com

Quote:
> Dear All

> I've long felt that using a date/time value for just a date is not a good
> idea: particularly if using it for a primary key. Double precision floats
> just don't make for good equality comparisons.

> The problem: 12345.9999999999 <> 12346.000000

> So I experimented with long integers. This seems to work: field format set
> to "Short Date" works fine, so does setting Default to =Date().

> So the question is, what are the problems with this approach. Are there
> any? Should we all be using this? Are you all using this already?

> B Wishes

> Tim F

Sun, 12 Dec 2004 03:41:36 GMT
Date as long integer
Biggest issue is compatibility.

You're pinning yourself down to a number system that may not work correctly
with other databases. If you go with the built-in date/time type, it's
Microsoft's problem to make that work correctly for imports, exports, or
attached tables in SQL Server, Oracle, dBase, BTrieve, etc.

Dates can be a real pain. There's an incompatibility with Excel in the
first 60 days of 1900. There are problems with treating date/time values as
real values prior to 12/30/1899. That gives me enough disquiet to question
whether the gains of integer math justify the maintenance issues you may be
creating for yourself.

My recommendation would be to stay with the Date/Time type, *and* always
use the built-in date functions for date maths.

--
Allen Browne - Microsoft MVP (Most Valuable Professional)
Perth, Western Australia.
Allen Browne's Database And Training.
Tips for MS Access users: http://users.bigpond.net.au/abrowne1

Quote:
> Dear All

> I've long felt that using a date/time value for just a date is not a good
> idea: particularly if using it for a primary key. Double precision floats
> just don't make for good equality comparisons.

> The problem: 12345.9999999999 <> 12346.000000

> So I experimented with long integers. This seems to work: field format
set
> to "Short Date" works fine, so does setting Default to =Date().

> So the question is, what are the problems with this approach. Are there
> any? Should we all be using this? Are you all using this already?

> B Wishes

> Tim F

Sun, 12 Dec 2004 10:32:05 GMT
Date as long integer
If you just want to ensure that users don't enter times into the field, I'd
use something like the following in the After Update event of controls bound
to that field:

Private Sub txtTestDate_AfterUpdate()

If IsDate(Me!txtTestDate) Then
Me!txtTestDate = DateSerial(Year(Me!txtTestDate),
Month(Me!txtTestDate), _
Day(Me!txtTestDate))
End If

End Sub

--
Brendan Reynolds (MVP)

Access 2000/2002 sample app at
http://brenreyn.brinkster.net
----------------------------

Quote:
> Dear All

> I've long felt that using a date/time value for just a date is not a good
> idea: particularly if using it for a primary key. Double precision floats
> just don't make for good equality comparisons.

> The problem: 12345.9999999999 <> 12346.000000

> So I experimented with long integers. This seems to work: field format set
> to "Short Date" works fine, so does setting Default to =Date().

> So the question is, what are the problems with this approach. Are there
> any? Should we all be using this? Are you all using this already?

> B Wishes

> Tim F

Sun, 12 Dec 2004 20:10:29 GMT
Date as long integer

Quote:

> If you just want to ensure that users don't enter times into the
> field, I'd use something like the following in the After Update event
> of controls bound to that field:

No, it's more fundamental than that. It comes from the old rounding
problem:

a date/time value created from (some date) + 0.75 does not always
work out the same as (some date) + 17/60 + 60/3600 or whatever...

a date/time value that accidentally has a time portion will get
missed by a query BETWEEN #date1# and #date2# (this produced a bug
that took me weeks to hunt down...)

generally using floats for keys seems like a bad idea, precisely
because they are not discreet -- and a date is quite often either
unique or part of a compound primary key.

I take the point about compatibility and conversions. Is there a separate
date type in SQLS or Oracle without the time part?

Maybe I'm making too much of this. I've not had problems with using
date/time values when working with dates -- just an uncomfortable feeling
using a continuous value to model a discrete one.

All the best

Tim F

Mon, 13 Dec 2004 02:47:21 GMT
Date as long integer

Quote:

> I guess if you want to rewrite all the built in date
> functionality ... I never had a problem comparing dates
> when defined as a date.  Please write an example of how
> you will use your long integer to calculate the number of
> days prior to today's date to demonstrate the superiority
> of this claim. Also calculate the third monday of the
> month following any given date.  If the given date is past
> the third monday of a month the correct result is the
> third monday of the following month.

I'm not sure I'm claiming superiority: the date functions all work as
planned either with implicit or explicit coercion.

Public Function DaysAgo(LongIntDate As Long) As Long
' this is the way I do it anyway...
DaysAgo = Fix(Now()) - LongIntDate

End Function

Public Function ThirdMonday(LongIntDate As Long) As Long
' This is also pretty much how you'd do it with
' datetime variables

Dim wOffset As Integer

wOffset = Weekday(DateSerial(Year(LongIntDate), _
Month(LongIntDate), _
1))

ThirdMonday = CLng(DateSerial(Year(LongIntDate), _
Month(LongIntDate), _
((vbMonday - wOffset + 7) Mod 7) + 15))

' *problem definition: should this be <= or < ?
If ThirdMonday <= LongIntDate Then

wOffset = Weekday(DateSerial(Year(LongIntDate), _
Month(LongIntDate) + 1, _
1))

ThirdMonday = CLng(DateSerial(Year(LongIntDate), _
Month(LongIntDate) + 1, _
((vbMonday - wOffset + 7) Mod 7) + 15))

End If

End Function

My issue is not about deficiencies in the date/time datatype. It's more
about controlling data values. Frequently one wants a discrete date value
only.

I had a bug that was very hard to track down when a field was set with
Now() rather than Date(), for example, and records failed to turn up when
they should with BETWEEN DateOne AND DateTwo. Similarly, SELECT blah WHERE
MyDate = #20002-06-26# will fail when there's a time component.

Similarly, we tell new users not to use equality comparisons with double-
precision datatypes (e.g. WHERE AgeAtStart = 12.333) but to use a range
(e.g. WHERE AgeAtStart BETWEEN 12.3325 AND 12.3335); and then go on to do
the same thing implicitly with dates.

In any case, maybe I'm making too much of it. No DBMS can have an
exhaustive list of datatypes that fits every paranoia of each user! The
question was, did anybody else to this and were there any problems: answer
"no".

B Wishes

Tim F

Mon, 13 Dec 2004 05:18:23 GMT
Date as long integer

<snip>

Quote:
>   a date/time value created from (some date) + 0.75 does not always
>   work out the same as (some date) + 17/60 + 60/3600 or whatever...

I wouldn't do that. I don't use the + or - operators with dates, except
occasionaly for simple addition and subtraction of whole days only. For

Quote:
>   a date/time value that accidentally has a time portion will get
>   missed by a query BETWEEN #date1# and #date2# (this produced a bug
>   that took me weeks to hunt down...)

The code I posted takes care of that. If you don't want a time part in a
date, don't permit it. If you're dealing with pre-existing data, use a query
to ensure you get just the data you want. In the example above, for example,
something like this should take care of it:

Quote:
>= #date1# AND < (#date2# + 1)

There are issues with dates, of course, but they're fairly well known at
this stage, and the work-arounds are readily available. Check out the
following URL:

http://www.mvps.org/access/datetime/index.htm

Also check out the following newsgroup post, and other posts in the same
thread. It was back in April, so is unlikely to be still available on your
news server, but you'll find it in the newsgroup archives at www.google.com

Newsgroups: comp.databases.ms-access
Subject: Peeve - inefficient date handling practices

Date: Sat, 13 Apr 2002 09:01:25 GMT

--
Brendan Reynolds (MVP)

Access 2000/2002 sample app at
http://brenreyn.brinkster.net
----------------------------

Mon, 13 Dec 2004 17:53:43 GMT
Date as long integer

Quote:
> If you just want to ensure that users don't enter times into the field,
I'd
> use something like the following in the After Update event of controls
bound
> to that field:

> Private Sub txtTestDate_AfterUpdate()

>     If IsDate(Me!txtTestDate) Then
>         Me!txtTestDate = DateSerial(Year(Me!txtTestDate),
> Month(Me!txtTestDate), _
>             Day(Me!txtTestDate))
>     End If

> End Sub

Brendan, is there an advantage to doing it that way rather than

Me!txtTestDate = DateValue(Me!txtTestDate)

?  I haven't compared the approaches for efficiency, but I'd expect one
function call to be more efficient than 4.  Have I overlooked something?

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

Tue, 14 Dec 2004 14:35:24 GMT
Date as long integer
You're probably right, Dirk. There is one difference. According to the help
file:

"If the date argument includes time information, DateValue doesn't return
it. However, if date includes invalid time information (such as "89:98"), an
error occurs."

As the object of the exercise here is to discard the time portion, there
doesn't seem to be much point in raising an error if it is invalid. But you
could, of course, still use DateValue and trap the error.

I usually use something like this in a form, where one record at a time is
being manually entered, and under those circumstances I wouldn't worry much
about optimizing this code - either version is going to run faster than
anyone can type, anyway. But of course, if we were doing this in a query or
looping through a recordset, then optimization would become much more
important, and your suggestion would certainly be well worth investigating.

--
Brendan Reynolds (MVP)

Access 2000/2002 sample app at
http://brenreyn.brinkster.net
----------------------------

Quote:

> > If you just want to ensure that users don't enter times into the field,
> I'd
> > use something like the following in the After Update event of controls
> bound
> > to that field:

> > Private Sub txtTestDate_AfterUpdate()

> >     If IsDate(Me!txtTestDate) Then
> >         Me!txtTestDate = DateSerial(Year(Me!txtTestDate),
> > Month(Me!txtTestDate), _
> >             Day(Me!txtTestDate))
> >     End If

> > End Sub

> Brendan, is there an advantage to doing it that way rather than

>     Me!txtTestDate = DateValue(Me!txtTestDate)

> ?  I haven't compared the approaches for efficiency, but I'd expect one
> function call to be more efficient than 4.  Have I overlooked something?

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

Tue, 14 Dec 2004 17:24:31 GMT
Date as long integer
Yes, but in that case (invalid time information) the Year, Month, and Day
function will also fail with an error.  Or at least they did when I tried it in
the debug window.

Sorry, I couldn't resist joining the discussion.

Quote:

> You're probably right, Dirk. There is one difference. According to the help
> file:

> "If the date argument includes time information, DateValue doesn't return
> it. However, if date includes invalid time information (such as "89:98"), an
> error occurs."

<SNIP>

Tue, 14 Dec 2004 19:26:39 GMT

 Page 1 of 2 [ 16 post ] Go to page: [1] [2]

Relevant Pages