A Function to Quote Literal Dates When Building SQL in VBA

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: #12/13/2021#.

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:

That doesn't seem right.

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.)

Oh, I see what happened now.🤦‍♂️

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.

The Original `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 Qt().

I kept these function names intentionally short to maintain a strong signal to noise ratio in my SQL building code.

"WHERE CreatedAt >= " & Dt(StartDate) & " AND CreatedAt < " & Dt(UpperDate)

I find this much easier to read...

"WHERE CreatedAt >= " & QuoteDate(StartDate) & " AND CreatedAt < " & QuoteDate(UpperDate)

...than this...

"WHERE CreatedAt >= #" & StartDate & "# AND CreatedAt < #" & UpperDate & "#"

...or this (which is also more likely to result in syntax errors for misplaced pound signs).

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.

Date Locales

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:

Date/Time in VBA and Access

The complete guide on date and time in your Access and VBA applications.

CodekabinettPhilipp Stiefel

Access/VBA - Formatting dates for a specific Locale

How to format a date for a language and country that is different from the current Windows regional settings.

CodekabinettPhilipp Stiefel

The ISO Date Format

While there are many ambiguous ways to write dates, there is one unambiguous international standard: yyyy-mm-dd hh:nn:ss.

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.