Queries...Date & Time

[mmohon]

Quick Description:
Ok, I have a table of events occuring for ER patients for an entire
month. I can isolate the event for when they see a doctor, but I want
to isolate it further by the time of day they come in. So basically if
they come in at 6 am, how long did it take them to see a doctor.

The problem:
The event time is stored as a Date Time together, how do I query all
the days of the month....but only between 1 am and 2 am etc. Is there
a way to extract only the time even, without looking at the date part
of it?

Also, if I subtract 1 date time from another, how can i set it to
format back to time and not some long decimal.

 

[Allen Browne]

You can extract the time part with TimeValue().

Use DateDiff() to get the difference between 2 dates. Examples in:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[Guest]

See VBA Help on the DateDiff and DatePart functions.

To calculate the difference in minutes between two Date/Time values, use n
as the interval parameter:

DateDiff("n", YourFirstDate,YourSecondDate)

To specify only those records between one and two am, create a calculated
field in a query, and specify 1 as the criteria (returning records from 1:00
am through 1:59:59):

LogInHour: DatePart("h",YourDateValue)

Hope that helps.
Sprinks

 

[Jamie Collins]

The event time is stored as a Date Time together, how do I query all
the days of the month....but only between 1 am and 2 am etc.

The granularity of DATETIME values in Access/Jet is one second. The SQL
BETWEEN construct is inclusive of both values, therefore the SQL
equivalent of the English 'between 1 am and 2' would be:

TIMEVALUE(event_start_date) BETWEEN #01:00:00# AND #01:59:59#

only between 1 am and 2 am etc.

Your 'etc' may mean it's a good idea to put the ranges in a two-column
table and joining to it using BETWEEN as above but with columns in
place of time literals.

Jamie.

--

 

[Douglas J. Steele]

The granularity of DATETIME values in Access/Jet is one second.

<picky>

I don't believe that's 100% accurate, Jamie.

While it's true that you can only explicitly address down to 1 second (i.e.:
there's no function to retrieve less than seconds, and the Format statement
has no way to go below seconds), it is possible to actually have sub-second
values stored in a Date/Time value.

Remember that the Date/Time field is an 8 byte floating point number, where
the decimal portion of the value represents the time as a fraction of a day.

?Format(#2006-08-03 13:01:01#, "General Number")
38932.5423726852
?Format(#2006-08-03 13:01:02#, "General Number")
38932.5423842593

There's a fairly sizable difference between the two values.

?38932.5423842593 - 38932.5423726852
1.15740986075252E-05

You could store time values of 38932.5423726852, 38932.5423726853 and so on
between those two values.

While Access can only display 38932.5423726852 as 2006-08-03 13:01:01, it
will recognize that there's a difference between the two. The following
routine illustrates what I mean:

Sub ShowDifference()
Dim dtmStart As Date
Dim dtmEnd As Date

dtmStart = #8/3/2006 1:01:01 PM#
dtmEnd = #8/3/2006# + 0.5423726852

Debug.Print "dtmStart = " & Format(dtmStart)
Debug.Print " dtmEnd = " & Format(dtmEnd)
Debug.Print "(dtmStart = dtmEnd) = " & (dtmStart = dtmEnd)
Debug.Print "(dtmEnd - dtmStart) = " & (dtmEnd - dtmStart)

End Sub

Running that results in the following:

dtmStart = 2006-08-03 13:01:01
dtmEnd = 2006-08-03 13:01:01
(dtmStart = dtmEnd) = False
(dtmEnd - dtmStart) = 1.45519152283669E-11


</picky>

 

[Jamie Collins]

<picky>

I don't believe that's 100% accurate, Jamie.

While it's true that you can only explicitly address down to 1 second (i.e.:
there's no function to retrieve less than seconds, and the Format statement
has no way to go below seconds), it is possible to actually have sub-second
values stored in a Date/Time value.

Remember that the Date/Time field is an 8 byte floating point number

Actually, I try to forget <g>. I know the implementation is not going
to change anytime soon but is there any need to be as vague as DATE() +
1 etc? I always say, "Use the temporal functions": the effect on
subsecond values is to round them, which I think is probably acceptable
in most cases.

Recently I've taken to constraining my DATETIME columns to ensure they
are of one second granularity, with SQL 'helper' procedures to remove
the subsecond elements.

Jamie.

--