Working with DateTimeExtended in Access

Source: Unsplash, photo by Jakub Dziubak

Many of you are aware of the datatype DateTimeExtended that was added to Microsoft Access a couple of years ago so that we could record time data in increments smaller than seconds. This data type has been available in databases like SQL Server for quite a few years now. The addition of this field type to Access, allows you to record precise time data with Microsoft Access, as well as connect to other sources that use precise time fields like DateTime2 in SQL Server. See this Microsoft Support article for details about this data type.

Documentation

There is still little documentation from Microsoft on how you can use this newish datatype in queries and VBA within Access. In fact, at least for now, VBA cannot handle using these data types with date functions at all! This does not mean you are necessarily totally stuck and cannot use VBA if you are using a DateTimeExtended field, but it does mean that you will need to take the additional step of first converting the field value to a string, then calling the function with a string argument.

The picture below illustrates a few of these issues in VBA. The Schedule table has a DateTimeExtended field named [Visit Date]. The two commented out lines both return a runtime error 13 Type mismatch. The first because the DatePart function in VBA cannot handle a DateTimeExtended type directly, the second because it cannot even handle it formatted as a string with the nanoseconds included. To get this to work, you would have to first strip off the nanoseconds, and send as a string formatted like a DateTime field. However, notice that the line that sets the DateTimeExtended field to a string which INCLUDES nanoseconds, does work and correctly updates the date.

Classic date functions

If you notice in the documentation on the Date/Time Extended data type, there is a Warning indicating there may be a loss of precision in calculations or other problems. It mentions that Microsoft is planning to better support expressions and functions in an upcoming release. The good news is that you can now use date functions correctly in queries! These functions generally take a variant, numeric expression, string expression, or any combination, that can represent a date as at least one argument. If the argument is a Date/Time Extended field, ACE will handle that appropriately and return the correct result. Documentation on these functions can be found in this Microsoft Learn post.

• Year

• Month

• Weekday

• Day

• Hour

• Minute

• Second

• DateAdd

• DateDiff

• DatePart

New extended date functions

In addition, queries can now make use of new Extended versions of some of these functions. These functions are currently undocumented, but feel free to try them (listed below). They work the same way as the function without the Extended at the end, but return a Date/Time Extended value instead of a Date/Time value.

• DateValueExtended

• TimeValueExtended

• DateSerialExtended

• TimeSerialExtended

• NowExtended

• DateExtended

• DateTimeExtended

• TimeExtended

Compatibility

Keep in mind that Date/Time Extended data type is only supported currently in Microsoft 365 and Access 2021. Using Date/Time Extended data type (as well as the still newish but slightly older Large Number (BigInt) data type also marks your database as a version not able to be opened by an older version of Access. When you add a field with this data type you do get a warning reminding you of this. If you are linking to a non-Access database like SQL Server, for instance, and want to make use of a field there as a Date/Time Extended data type, you must also, under Access Options - Current Database - Data Type Support Options, check the "Support Date Time Extended (DateTime2) Data Type for Linked/Imported Tables" option. This then marks your database as a newer version and maps DateTime2 fields in SQL Server to Date/Time Extended data types in Access. If you do not set this option, then the field will get mapped as a text field and your frontend database will not have the newer version mark.

Note that setting this option on your frontend database is not necessary if you are linking to an Access backend database with a Date/Time Extended field type. However, you still would not be able to open the link to the Access backend if you had an older version of Access because the backend would have the newer version mark.

You tell us - is this data type something you are eager to make use of? Are you excited about being able to use Date/Time Extended fields in queries? How anxious are you to make better use of them in VBA and why?