Using the Date/Time Extended data type

The Date/Time Extended data type stores date and time information and is similar to the Date/Time data type, but it provides a larger date range, a higher fractional precision, and compatibility with the SQL Server datetime2 date type. When you import or link Access data to SQL Server, you can consistently map an Access Date/Time Extended field to a SQL Server datetime2 column. For more information, see datetime2 (Transact-SQL).

Date/Time Extended range

Warning    When you create expressions and use date/time functions based on the Date/Time Extended data type in Access, you may lose precision in calculations or encounter other problems with the results. We are aware of this issue and plan to better support expressions and functions in an upcoming release. As a workaround, you can Create a pass-through query to use the equivalent SQL Server expression and date/time functions. For more information, see Comparing Access SQL with SQL Server TSQL.

In this article

Comparing Date/Time and Date/Time Extended data types

Using the Date/Time Extended data type

Backward compatibility considerations

Using the Date/Time Extended data type as a string in VBA

Comparing Date/Time and Date/Time Extended data types

The following tables summarizes important differences between the two data types.

Attribute

Date/Time

Date/Time Extended

Minimum Value

100-01-01 00:00:00

0001-01-01 00:00:00

Maximum Value

9999-12-31 23:59:59.999

9999-12-31 23:59:59.9999999

Accuracy

0.001 seconds

1 nanosecond

Size

Double-precision floating point

Encoded string of 42 bytes

Top of Page

Using the Date/Time Extended data type

The following information describes important usage considerations.

Table Design View    To take advantage of the larger data range and higher precision, you can add a field to an Access table. You can also convert a Date/Time to Date/Time Extended data type in Table Design view. Use of this data type as a primary key field is also supported. For more information, see Create a table and add fields.

Entering the date and time   Entering date and time values is similar to the Date/Time data type, except that you can also enter a fractional nanosecond. For example:

  • Entry format: mm/dd/yyyy hh:mm:ss.nnnnnnn

  • Example: 06/15/1215 09:25:3.234

If there are more than 7 fractional nanoseconds, they are rounded to 7 digits. To control the display of the fractional nanoseconds, open the Table, on the ribbon select Fields, and in the Formatting group, select Increase Decimals Button image or Decrease Decimals Button image .

Formatting    Both the Date/Time and Date/Time Extended data types use similar standard formatting strings of General Date, Long Date, Medium Date, Short Date, Long Time, Medium Time, and Short Time and both support custom formatting. For the Date/Time Extended data type, the time-based standard formats also support fractional precision for nanoseconds. Formatting of the Date/Time Extended data type defaults to General Date and Long Time formats and follows the options specified in the Windows regional settings. You can also control the formatting of the fractional precision by using the Decimal Places property to specify the number of digits to the right of the decimal point (1-7).

Link and import     You can also link to or import from databases with a corresponding data type, such as the SQL Server datetime2 data type. SQL Server version 2014 or later databases are supported. The Date/Time Extended data type requires the use of Microsoft ODBC Driver for SQL Server 11 or later. We recommend using Microsoft ODBC Driver 13.1 for SQL Server. The use of OLE DB is also supported. For more information, see Data Type Support for ODBC Date and Time Improvements and Use Enhanced Date and Time Features (OLE DB).

Forms and reports     You can add the Date/Time Extended data type to a form or report. In a form you can use the date picker and the input mask to enter a date with the larger range, but not the fractional precision for nanoseconds.

Expression Support   The Date/Time Extended data type supports SQL Aggregate Functions and expression evaluation. For example, using LoggedDateTime as a field with the Date/Time Extended data type:

Task

Example

Result

Find the minimum value

Min(LoggedDateTime)

The earliest date and time within the range

Extract the month

Month(LoggedDateTime)

The month name, such as January

Add one day

[LoggedDateTime]+1

Tuesday would become Wednesday

Top of Page

Backward Compatibility Considerations

The Date/Time Extended data type is not compatible with non-subscription versions of Microsoft Access. As a result, if the data type is implemented within a local Access table and the Access database is used with a non-subscription version of Access, you can’t open the database.

You can enable or disable the Date/Time Extended data type for linking and importing operations with the Current Database Access option Support Date Time Extended (DateTime2) Data Type for Linked/lmported Tables. For more information, see Set user options for the current database.

Top of Page

Using the Date/Time Extended data type as a string in VBA

The following VBA examples use DAO methods to display, enter, and evaluate the Date/Time Extended data type based on the table below.

ID

DTEData

DTData

1

1/1/2 1:01:03.1234567 AM

1/1/2001

Table name:    DTETable
ID data type:    Autonumber
DTEData data type:    Date/Time Extended
DTData data type:    Date/Time

Example: Displaying the date and time

The following example displays the date and time. The format used is mm/dd/yyyy hh:mm:ss.nnnnnnn in 24 hour clock. The format is not customizable.

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("DTETable")
Do Until rs.EOF
    Debug.Print rs!DTETable
    rs.MoveNext
Loop

Result    Access displays: 01/01/0002 01:01:03.1234567.

Example: Entering the date and time

The following example enters the date and time using a string format. All standard date and time formats are supported.

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("DTETable")
With CurrentDb.OpenRecordset("DTETable")
    .AddNew
    ![DTEData] = "1/1/9999 1:1:1.0123 AM"
    ![DTData] = #1/1/2001#
    .Update
End With

Result    Access adds a new row (ID = 2):

ID

DTEData

DTData

1

1/1/2 1:01:03.1234567 AM

1/1/2001

2

1/1/9999 1:01:01.0123000 AM

1/1/2001

Example: Evaluating a query expression

The following example uses the Day Function to extract the day number from the date and time fields.

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Day(DTEData) as day FROM DTETable")
Do Until rs.EOF
    Debug.Print "The day of the month is: "&rs!day
    rs.MoveNext
Loop

Result    Access displays:

The day of the month is: 1
The day of the month is: 1

Top of Page

See Also

Introduction to data types and field properties

Format a date and time field

Create or delete a date and time field

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×