> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# A Function to Quote Literal Dates When Building SQL in VBA
- URL: https://nolongerset.com/dt-function/
- Published: 2021-12-14T04:47:20.000Z
- Updated: 2026-05-08T12:52:49.000Z
- Description: This improved version of my date-quoting convenience function eliminates a common source of date-handling ambiguity via the ISO date format.
- Author: Mike Wolfe
- Tags: Code Library, #StringFunctions, SQL, #Import 2026-05-20 02:59

***UPDATE*** *\[2022-12-19\]: There is a [new version of this function](https://nolongerset.com/dt-function-v2/) 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:

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/12/image-23.png)

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

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/12/image-24.png)

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

What we have here is a **[logic error](https://nolongerset.com/logic-errors/)**. 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](https://nolongerset.com/quoth-thy-sql-evermore/) 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.

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

I find this much easier to read...

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

...than this...

```vba
"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. 

```vba
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](https://en.wikipedia.org/wiki/Date%5Fformat%5Fby%5Fcountry):

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/12/image-26.png)

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](https://codekabinett.com/index.php?Lang=2).

Here are two particularly relevant articles:

[Date/Time in VBA and AccessThe complete guide on date and time in your Access and VBA applications.CodekabinettPhilipp Stiefel![](https://codekabinett.com/images/mvp_hor.png)](https://codekabinett.com/rdumps.php?Lang=2&targetDoc=date-time-data-type-vba-access)

[Access/VBA - Formatting dates for a specific LocaleHow to format a date for a language and country that is different from the current Windows regional settings.CodekabinettPhilipp Stiefel![](https://codekabinett.com/images/mvp_hor.png)](https://codekabinett.com/rdumps.php?Lang=2&targetDoc=format-date-language-country-vba-access)

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

```vba
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
```

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/12/image-25.png)

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](https://nolongerset.com/python-inspired-doc-tests-in-vba/):

```vba
'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](https://nolongerset.com/betweendates/#commento-comment-name-e354fdc97c6b241670d62bfc36158b66673f9dc45e226c74848fc527ed6ce15b) on a [recent post](https://nolongerset.com/betweendates/) inspired me to write today's article.

### Referenced articles

[Logic ErrorsThe logic error is the most dangerous and insidious of all software errors.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/08/cars-884673_1920.jpg)](https://nolongerset.com/logic-errors/)

[Quoth thy SQL? Evermore!Solving the “O’Malley problem” with dedicated functions to sanitize our strings. Little Bobby Tables would be proud.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/09/crow-3347677_1920-1.jpg)](https://nolongerset.com/quoth-thy-sql-evermore/)

[A Safer Alternative to BETWEEN When Filtering DatesThis convenient function generates defensive SQL statements to help you avoid the subtle dangers of the BETWEEN statement when filtering dates.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/12/architecture-3223843_1920.jpg)](https://nolongerset.com/betweendates/)

*Image by [PublicDomainPictures](https://pixabay.com/users/publicdomainpictures-14/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=218233) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=218233)*