About a year ago, I wrote about a convenience function named Dt() that I use to wrap literal VBA date values in pound signs when generating dynamic SQL strings.
v0: My Original Function
My original version of the function–we'll call it version 0–was simple and straightforward:
Function Dt(DateVal As Variant) As String Const Pound As String = "#" If IsNull(DateVal) Then Dt = "Null" Else Dt = Pound & DateVal & Pound End If End Function
Here is some sample usage:
v1: My Updated "International" Version
I wrote the following in my article where I first introduced the
While there are many ambiguous ways to write dates, there is one unambiguous international standard:
We're going to force our literal dates into this ISO format when building SQL statements by expanding on the
Dt()function from above. Namely, we will be using the VBA Format() function to convert the literal date value into the ISO format.
Here was the updated function:
Function Dt(DateVal As Variant) As String Const Pound As String = "#" If IsNull(DateVal) Or IsEmpty(DateVal) Then Dt = "Null" Else Dt = Pound & Format(DateVal, "yyyy-mm-dd hh:nn:ss") & Pound End If End Function
And here is some sample usage:
As you can see in the second example above, the updated Dt() function produces an unambiguous date-time format that should be interpreted the same way anywhere in the world. We may run into problems with date-only values, such as in the first example. But we will definitely run into problems with time-only values, such as in the third example, if we are generating dynamic T-SQL to run in SQL Server.
Why does v1 of my Dt() function break for time-only values? Because of the "epoch" differences between VBA and SQL Server:
The SQL Server epoch is January 1, 1900.
The VBA epoch is December 30, 1899.
The problem, then, arises when you try to store a time-only value from VBA to a SQL Server
datetime field by first converting that value to an ISO string datetime format.
If we use a time-only string to assign a time-only value to a
datetime field, it will save it using the SQL Server epoch date:
SET OccursAt = '3:00 PM' -- stores a datetime value of 1900-01-01 15:00:00 -- gets treated as a time-only value
This is important because Access will automatically translate a datetime SQL Server value from
1899-12-30 (the Access/VBA epoch) behind the scenes. Thus, Access will treat that value as a time-only date.
However, if we explicitly convert a VBA time-only value to the ISO string datetime format and store that value in a SQL Server datetime field, then Access no longer sees it as a time-only value.
SET OccursAt = '1899-12-30 15:00:00' -- stores a datetime value of 1899-12-30 15:00:00 -- gets treated as a date *and* time value -- from the year 1899
v2: Proper Handling of Date-Only and Time-Only Values
Here is the updated Dt() function.
The comments that start
'>>> are Python-inspired DocTests.
' ---------------------------------------------------------------- ' Procedure : Dt ' Updated : 2022-12-19 (v2) ' Author : Mike Wolfe ' Source : https://nolongerset.com/dt-function-v2/ ' Purpose : Wraps date values in Access date literal tokens (#) ' and converts values to ISO string formatting for ' maximum compatibility across back-end databases and locales ' Notes - If passed a Null value this function will return the text 'Null'; ' this allows the function to be used to build SQL conditionals ' without extra handling ' - Date-only values do not include a time portion ' - Time-only values do not include a date portion ' (this avoids subtle bugs due to epoch differences) ' ---------------------------------------------------------------- '>>> Dt(#19-Dec-2022#) ' #2022-12-19# '>>> Dt(#19-Dec-2022 6:00 PM#) ' #2022-12-19 18:00:00# '>>> Dt(#6:00 PM#) ' #18:00:00# '>>> Dt("December 19, 2022") ' #2022-12-19# '>>> Dt(0) ' #00:00:00# '>>> Dt(#12 AM#) ' #00:00:00# '>>> Dt(DateSerial(2018, 2, 4)) ' #2018-02-04# '>>> Dt("Gibberish") ' #Gibberish# '>>> IsNull(Dt(Null)) ' False '>>> "_" & Dt(Null) & "_" ' _Null_ '>>> Dt("Null") ' #Null# Function Dt(DateVal As Variant) As String Const Pound As String = "#" If IsNull(DateVal) Or IsEmpty(DateVal) Then Dt = "Null" Else Dt = Pound & Format(DateVal, "yyyy-mm-dd hh:nn:ss") & Pound End If If Dt Like "[#]1899-12-30 *" Then 'Strip out the epoch date from a time-only value Dt = Replace(Dt, "1899-12-30 ", "") Else 'Strip out the time portion from a date-only value Dt = Replace(Dt, " 00:00:00", "") End If End Function
Cover image created with Microsoft Designer