A Function to Quote Literal Dates When Building SQL in VBA
This improved version of my date-quoting convenience function eliminates a common source of date-handling ambiguity via the ISO date format.
UPDATE [2022-12-19]: There is a new version of this function that addresses a bug that occurred when saving time-only values to SQL Server.
Tonight at dinner, my daughter was telling a story about a classmate of hers being disrespectful at school. My daughter said, "She was eating Cheetos with her feet up on the desk."
To which I responded, "Why was she sitting on the desk?" (Truly, one of the best parts of being a dad are the dad jokes.)
While unintentional ambiguity can lead to hilarity in daily conversation, it's not so funny when it arises in business applications.
Quoting Dates in SQL
To quote literal dates in the Jet/ACE SQL dialect, you need to enclose them in pound signs:
If you fail to do that, the query will likely still execute. However, it almost certainly won't be doing what you want. Unless what you want is for it to interpret
12/13/2021 as 39 seconds after midnight:
What's going on here? Nothing but simple math, really:
- Divide 12 by 13
- Divide that result by 2,021
- Convert the resulting fraction to the time of day (e.g., 0.25 is 6 AM; 0.5 is noon; etc.)
What we have here is a logic error. Those are the types of errors that can cause the most damage because they can go unnoticed for so long.
Dt(): A Convenience Function
Many years ago, I wrote a function that I named
Dt() as a sidekick to a string-quoting function that I named
I kept these function names intentionally short to maintain a strong signal to noise ratio in my SQL building code.
The first iteration of the
Dt() function was very much just a way to improve the readability of my code. It had very little logic in it.
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
There is absolutely nothing wrong with this code. I've used it for over 10 years with no issues. It saves some typing and it eliminates SQL syntax errors related to misplaced pound signs.
But, I think we can improve it.
If you've only ever written code in the United States for end users who will only ever run code in the United States (like I have), then you've probably never had to worry about locale settings (or translations or Unicode or a whole bunch of other stuff).
Well, I happen to have it on pretty good authority that there are, in fact, other countries outside of the United States. And those other countries do all sorts of weird things, like:
- wear shorts when it's only 25° outside
- mix up their commas and periods when writing numbers
- play football with a sphere
- add letters to their words (for a bit of extra "flavour," I guess)
- celebrate Christmas on 25/12
That last point has major implications for us database developers. It turns out that there are several ways that cultures around the world write dates:
Anyway, as a US-based programmer that's only ever developed applications for US-based users, I've never (had to) put much thought into things like "Windows Locale Settings." Honestly, you're better off learning the details from someone far more knowledgeable than me on this topic. My go-to for these sorts of internationalization issues in Access is Philipp Stiefel and his blog, codekabinett.com.
Here are two particularly relevant articles:
The ISO Date Format
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.
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
I'm including the time portion of the DateVal variable using the 24-hour ISO format. This guarantees that the code will work as expected if we pass a DateVal that includes a time component. For DateVal's that have no time component, the inclusion of
00:00:00 at the end of the date literal will have no ill effects.
Full Code Sample
Here is the full code sample, complete with DocTests:
'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 ' - The Dt() (date-quoting) function converts literal dates into the ISO date format ' for maximum compatibility between back-end databases and user-specific locale settings '>>> Dt(DateSerial(2018, 2, 4)) ' #2018-02-04 00:00:00# '>>> 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 End Function
Special thanks to Ben Clothier, whose comment on a recent post inspired me to write today's article.
Image by PublicDomainPictures from Pixabay