Dt() Function v2: Handling Time-Only Date Values

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

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

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

Sample Usage

Referenced articles

Cover image created with Microsoft Designer