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).
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
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 |
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 
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 |
Backward Compatibility Considerations
The Date/Time Extended data type is not compatible with previous versions of Microsoft Access. If the type is used within a local Access table, versions of Access that do not include the feature will not be able to 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 Data Type for Linked/lmported Tables. For more information, see Set user options for the current database.
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
LoopResult 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 WithResult 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
LoopResult Access displays:
The day of the month is: 1
The day of the month is: 1
