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

# Dt() Function v2: Handling Time-Only Date Values
- URL: https://nolongerset.com/dt-function-v2/
- Published: 2022-12-20T04:20:41.000Z
- Updated: 2026-05-08T12:52:50.000Z
- Description: An important bug fix for my previously published date-wrapping VBA function that addresses an issue integrating time-only values with SQL Server.
- Author: Mike Wolfe
- Tags: Code Library, #StringFunctions, #Import 2026-05-20 02:59

About a year ago, I [wrote about](https://nolongerset.com/dt-function/) 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:

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

Here is some sample usage:

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

## v1: My Updated "International" Version

I wrote the following in my article where I first introduced the `Dt()` function:

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

Here was the updated function:

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

And here is some sample usage:

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

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](https://en.wikipedia.org/wiki/Epoch%5F%28computing%29)*" differences between VBA and SQL Server:

[Epoch Differences Between VBA and SQL ServerDo you have unexplained dates of December 30, 1899 in your SQL Server database? VBA’s epoch (i.e., zero date) could be the culprit.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/12/Epoch-Differences-Between-VBA-and-SQL-Server.png)](https://nolongerset.com/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:

```sql
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 `1900-01-01` to `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.

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

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

#### Sample Usage

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

---

### Referenced articles

[A Function to Quote Literal Dates When Building SQL in VBAThis improved version of my date-quoting convenience function eliminates a common source of date-handling ambiguity via the ISO date format.![](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/cheese-curls-218233_1280.jpg)](https://nolongerset.com/dt-function/)

*Cover image created with [Microsoft Designer](https://designer.microsoft.com/)*