ACC: Storing, Calculating, and Comparing Date/Time Data |
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article explains how Microsoft Access stores the Date/Time data
type and why you may receive unexpected results when you calculate or
compare dates and times.
This article addresses the following topics:
- Storing Date/Time Data
- Formatting a Date/Time Field
- Calculating Time Data
- Comparing Date Data
- Comparing Time Data
MORE INFORMATIONStoring Date/Time Data
Microsoft Access stores the Date/Time data type as a double-precision,
floating-point number (up to 15 decimal places). The integer portion of
the double-precision number represents the date; the decimal portion
represents the time.
Valid date values range from -647,434 (January 1, 100 A.D.) to 2,958,465
(December 31, 9999 A.D.). A date value of 0 represents December 30, 1899.
Microsoft Access stores dates prior to December 30, 1899 as negative
numbers.
Valid time values range from .0 (00:00:00) to .99999 (23:59:59). The
numeric value represents a fraction of one day. You can convert the
numeric value into hours, minutes, and seconds by multiplying the numeric
value by 24.
The following table illustrates how Microsoft Access stores Date/Time
values:
Double Date Actual Time Actual
Number Portion Date Portion Time
------------------------------------------------------------------
1.0 1 December 31,1899 .0 12:00:00 A.M.
2.5 2 January 1, 1900 .5 12:00:00 P.M.
27468.96875 27468 March 15, 1975 .96875 11:15:00 P.M.
33914.125 33914 November 6, 1992 .125 3:00:00 A.M.
To view how Microsoft Access stores Date/Time values as numbers, type the
following commands in the Debug window in Microsoft Access 97 and 7.0 (or
the Immediate window in Microsoft Access 2.0 or earlier), and note the
results:
? CDbl(#5/18/87 14:00:00#) 31915.5833333333 ? CDbl(#12/14/1849 17:32:00#) -18278.7305555556
To view the date and time of numeric values, type the following commands
in the Debug window (or the Immediate window), and note the results:
? CVDate(1.375) 12/31/1899 9:00:00 AM ? CVDate(-304398.575) 8/1/1066 1:48:00 PM Formatting a Date/Time Field
You can format a Date/Time value to display a date, a time, or both. When
you use a date-only format, Microsoft Access stores a value of 0 for the
time portion. When you use a time-only format, Microsoft Access stores a
value of 0 for the date portion.
The following table illustrates how Microsoft Access stores Date/Time
values and how you can display those values using different formats:
Stored Value Default Format Custom Format
(Double Number) (General Date) (mm/dd/yyyy hh:nn:ss AM/PM)
---------------------------------------------------------------------
34834.0 5/15/95 05/15/1995 12:00:00 A.M.
0.546527777777778 1:07 PM 12/30/1899 01:07:00 P.M.
34041.9375 3/13/93 10:30PM 03/13/1993 10:30:00 P.M.
NOTE: The default format for a Date/Time value is General Date. If a value
is date-only, then no time is displayed. If the value is time-only, then
no date is displayed.
Calculating Time Data
Because a time value is stored as a fraction of a 24-hour day, you may
receive incorrect formatting results when you calculate time intervals
greater than 24 hours. To work around this behavior, you can create a
user-defined function to ensure that time intervals are formatted
correctly.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
NOTE: Visual Basic for Applications is called Access Basic in Microsoft
Access versions 1.x and 2.0. For more information about Access Basic,
please refer to the "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" manual in Microsoft
Access version 2.0
To calculate and format time intervals correctly, follow these steps:
- Create a module and type the following line in the Declarations section if it is not already there:
Option Explicit - Type the following procedure.
NOTE: In the following sample code, an underscore (_) at the end of a
line is used as a line-continuation character. Remove the underscore
from the end of the line when re-creating this code in Access Basic.
'------------------------------------------------------------------
' This function calculates the elapsed time between two values and
' formats the result in four different ways.
'
' The function accepts interval arguments such as the following:
'
' #5/12/95 6:00:00AM# - #5/11/95 10:00:00PM#
'
' -or-
'
' [End Time]-[Start Time]
'------------------------------------------------------------------
Function ElapsedTime (Interval)
Dim x
x = Int(CSng(Interval * 24 * 3600)) & " Seconds"
Debug.Print x
x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
& " Minutes:Seconds"
Debug.Print x
x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
& " Hours:Minutes:Seconds"
Debug.Print x
x = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
& " Hours " & Format(Interval, "nn") & " Minutes " & _
Format(Interval, "ss") & " Seconds"
Debug.Print x
End Function - Type the following line in the Debug window (or the Immediate window), and then press ENTER:
? ElapsedTime(#6/1/93 8:23:00PM#-#6/1/93 8:12:12AM#)
Note that the following values are displayed:
43848 Seconds
730:48 Minutes:Seconds
12:10:48 Hours:Minutes:Seconds
0 days 12 Hours 10 Minutes 48 Seconds
Comparing Date Data
Because dates and times are stored together as double-precision numbers,
you may receive unexpected results when you compare Date/Time data. For
example, if you type the following expression in the Debug window (or the
Immediate window in earlier versions), you receive a false (0) result even
if today's date is 3/31/95:
? Now()=DateValue("3/31/95")
The Now() function returns a double-precision number representing the
current date and time. However, the DateValue() function returns an
integer number representing the date but not a fractional time value. As a
result, Now() equals DateValue() only when Now() returns a time of
00:00:00 (12:00:00 A.M.).
To receive accurate results when you compare date values, use one of the
functions below. To test each function, type it in the Debug window (or
the Immediate window), substitute the current date for 3/31/95, and then
press ENTER:
- To return an integer value, use the Date() function:
?Date()=DateValue("3/31/95") To remove the fractional portion of the Now() function, use the Int()
function:
?Int(Now())=DateValue("3/31/95")
Comparing Time Data (in Microsoft Access 2.0)
When you compare time values, you may receive inconsistent results because
a time value is stored as the fractional portion of a double-precision,
floating-point number. For example, if you type the following expression
in the Immediate window, you receive a false (0) result even though the
two time values look the same:
NOTE: This behavior no longer occurs in Microsoft Access 7.0 or 97.
var1 = #2:00:00 PM#
var2 = DateAdd("n", 10, var1)
? var2 = #2:10:00 PM#
When Microsoft Access converts a time value to a fraction, the calculated
result may not be the exact equivalent of the time value. The small
difference caused by the calculation is enough to produce a false (0)
result when you compare a stored value to a constant value.
To receive accurate results when you compare time values, use one of the
methods below. To test each method, type it in the Immediate window, and
then press ENTER:
- Add an associated date to the time comparison:
var1 = #1/1/90 2:00:00 PM#
var2 = DateAdd("n", 10, var1)
? var2 = #1/1/90 2:10:00 PM# Convert the time values to String data types before you compare them:
var1 = #2:00:00 PM#
var2 = DateAdd("n", 10, var1)
? CStr(var2) = CStr(#2:10:00 PM#) Use the DateDiff() function to compare precise units such as seconds:
var1 = #2:00:00 PM#
var2 = DateAdd("n", 10, var1)
? DateDiff("s", var2, #2:10:00 PM#) = 0
REFERENCES
For more information about calculating time values, please see the
following article in the Microsoft Knowledge Base:
Q88657
ACC: Functions for Calculating and Displaying Date/Time Values
For more information about how to format Date/Time data types, type
"formatting dates" in the Office Assistant, click Search, and then click to
view "Format Property - Date/Time Data Type."
Additional query words:
lapsed time
Keywords : kbprg kbusage TblDatyp
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto
|
|
|