ACC2000: Storing, Calculating, and Comparing Date/Time Data

ID: Q210276

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

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 INFORMATION

Storing 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 -657,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 before 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.
    36836.125     36836     November 6, 2000   .125       3:00:00 A.M.

To view how Microsoft Access stores Date/Time values as numbers, type the following commands in the Immediate window, press ENTER, and note the results:

?CDbl(#5/18/1999 14:00:00#)

Result equals: 36298.5833333333

?CDbl(#12/14/1849 17:32:00#)

Result equals: -18278.7305555556

To view the date and time of numeric values, type the following commands in the Immediate window, press ENTER, and note the results:

?CVDate(1.375)

Result equals: 12/31/1899 9:00:00 AM

?CVDate(-304398.575)

Result equals: 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)
   ---------------------------------------------------------------------

   36295.0                 5/15/99           05/15/1999 12:00:00 AM
       0.546527777777778   1:07 PM           12/30/1899 01:07:00 PM
   36232.9375              3/13/99 10:30PM   03/13/1999 10:30:00 PM

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.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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:


'------------------------------------------------------------------
'  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 Immediate window, and then press ENTER:
? ElapsedTime(#6/1/1999 8:23:00PM#-#6/1/1999 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 Immediate window, you receive a False result even if today's date is 3/31/1999:

? Now()=DateValue("3/31/1999")

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 Immediate window, substitute the current date for 3/31/1999, and then press ENTER:

To return an integer value, use the Date() function:

?Date()=DateValue("3/31/1999")

To remove the fractional portion of the Now() function, use the Int() function:

?Int(Now())=DateValue("3/31/1999")

Comparing Time Data

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:

var1 = #2:01:00 PM#
var2 = DateAdd("n", 10, var1)
? var2 = #2:11: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/99 2:01:00 PM#
var2 = DateAdd("n", 10, var1)
? var2 = #1/1/99 2:11:00 PM#

Convert the time values to String data types before you compare them:

var1 = #2:01:00 PM#
var2 = DateAdd("n", 10, var1)
? CStr(var2) = CStr(#2:11:00 PM#)

Use the DateDiff() function to compare precise units such as seconds:

var1 = #2:01:00 PM#
var2 = DateAdd("n", 10, var1)
? DateDiff("s", var2, #2:11:00 PM#) = 0

REFERENCES

For additional information about calculating date and time values, please see the following article in the Microsoft Knowledge Base:

Q210604 ACC2000: Functions for Calculating and Displaying Date/Time Values

For more information about how to format Date/Time data types, click Microsoft Access Help on the Help menu, type format property - date/time data type in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: inf

Keywords : kbprg kbdta AccCon TblDatyp KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto