This article is contributed. See the original author and article here.
The Access team has implemented a new data type: Date & Time Extended, which enhances syntax compatibility with SQL server, and increases the accuracy and level of detail in records that include dates and times. This investment aligns with our broader goal of building Access apps on top of data that can be stored in the cloud.
See attributes:
|
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 |
As indicated in the table above, this new data type has a larger date range and greater fractional precision than the existing Date & Time data type in Access. However, Access’s current Date & Time data type will continue to operate as-is; we will not remove it.
SQL Compatibility
Our new Date & Time Extended type is compatible with SQL’s DateTime2 type; therefore, when you import or link to a SQL Server table, you can map the Access Date & Time Extended field to SQL’s Datetime2 field without losing date range or time precision.
Keep in Mind
- This supports SQL Server version 2016 and after.
- Date & Time Extended requires the use of Microsoft ODBC Driver for SQL Server 11 or later. We recommend using Microsoft ODBC Driver 13.1 for SQL Server.
Backward Compatibility
The Date/Time Extended data type is supported in Microsoft Access 365 but is not compatible with non-subscription (perpetual) versions of Microsoft Access. As a result, if the data type is implemented within a local Access table and that Access database is shared with a non-subscription version of Access, you will not be able to open the database in your non-subscription version.
Expression Support
You can use a number of expressions on our data type, including DateAdd, DateDiff, and more (see full list of expressions here).
For more advanced developers, you can also leverage SQL Aggregate Functions and expressions. For example, you can use 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 |
Using the Date/Time Extended data type as a string in VBA
As of today, you cannot perform calculations on the data type in VBA code, however you can use the new data type in VBA as text (string). The following VBA example uses Data Access Object 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
If you’d like to reference the type in VBA code, please be aware that expressions on the type are not supported, given that the data will be casted as a text. However, performing calculations of the new type in VBA code is an investment that the Access team is planning for at a later date.
Formatting
All formats for Date/Time are supported for Date/Time Extended.
|
Format |
Description |
Example |
|
General Date |
(Default) Displays date values as numbers and time values as hours, minutes, and seconds followed by AM or PM. |
06/30/2018 10:10:42.1234567 AM |
|
Long Date |
Displays only date values, as specified by the Long Date format in your Windows regional settings. |
Monday, August 27, 2018 |
|
Medium Date |
Displays the date as dd/mmm/yy, but uses the date separator specified in your Windows regional settings. |
27/Aug/18 27-Aug-18
|
|
Short Date |
Displays date values, as specified by the Short Date format in your Windows regional settings. |
8/27/2018 8-27-2018
|
|
Long Time |
Displays hours, minutes, and seconds followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings. |
10:10:42.1234567 AM |
|
Medium Time |
Displays hours and minutes followed by AM or PM. Access uses the separator specified in the Time setting in your Windows regional settings. |
10:10.1234567 AM |
|
Short Time |
Displays only hours and minutes. Access uses the separator specified in the Time setting in your Windows regional settings. |
10:10.1234567 |
For more information, see Using the Date/Time Extended data type.
On behalf of the Access team, we’d appreciate your feedback, and we hope you enjoy this new feature! :smiling_face_with_smiling_eyes:
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments