Code Library

Dt() Function v3: Refactoring with Automated Tests

Reader Brenda Bachtold provides a more readable version of my date-wrapping function. Automated doc tests prove that her version functions the same as mine.

Mike Wolfe

Dec 20, 2022 • 5 min read

Yesterday, I published an update to my SQL date-wrapping function, Dt().

Included with the code was a set of 11 Python-inspired doc tests. I love the doc tests concept for a few reasons:

It's a concise and effective way to show how a function works.
The documentation doesn't go stale.
Writing tests is simple and easy.
It allows for safe and confident refactoring.

That last feature is the one I want to highlight with today's article.

Dt() Function v2: Functional But Hard to Read

For reference, here is version 2 of my Dt() 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
    
    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

It's functional, but the logic is a bit convoluted.

Dt() Function v3: A More Readable Version

In the comments section of that article, reader Brenda Bachtold posted a more readable version of the function:

Public Function Dt(d As Variant) As String
    Const Pound As String = "#"
    
    Select Case True
        'null or empty
        Case IsNull(d), IsEmpty(d)
           Dt = "Null"
          
        'time only
        Case Format(d, "yyyy-mm-dd") = "1899-12-30"
            Dt = Pound & Format(d, "hh:nn:ss") & Pound
          
        'date only
        Case Format(d, "hh:nn:ss") = "00:00:00"
            Dt = Pound & Format(d, "yyyy-mm-dd") & Pound
        
        Case Else
            Dt = Pound & Format(d, "yyyy-mm-dd hh:nn:ss") & Pound
    End Select
End Function

My first thought when I saw that function was that it's a lot easier to follow than my version. My second thought was, "I wonder if it produces identical results, especially when it comes to edge cases?"

Luckily for me, I didn't have to rely (entirely) on my ability to translate the logic in my head to know that Brenda's refactored version was a drop-in replacement for my version of the function. All I had to do was copy and paste her code over mine and execute the doc tests. As long as all the tests passed, then I could be sure that her version was functionally equivalent to mine.

As I expected, all eleven tests passed:

The Value of Automated Tests

This refactoring demonstrates the value of automated tests.

This was a relatively simple function, but you can see how automated tests would give you the confidence to tackle refactoring projects on some rather complex code.

In this case, the automated tests are Python-inspired doc tests, but the concept applies equally to other kinds of automated tests, such as unit tests or integration tests. By having an existing set of tests that validate our expected outputs, we can safely and confidently refactor our code without worrying about breaking backwards compatibility.

As long as the tests pass, the implementation does not matter.

A Caveat Regarding Test Coverage

One thing to keep in mind is that the quality and quantity of your tests matter a great deal when it comes to refactoring with confidence.

The more edge cases you can include in your test suite, the more confident you can be in your refactoring. Along those same lines, if your calling code relies on some specific output for a given set of inputs, you'll want to document that functionality with a test. That way, if you refactor that function at some point in the future, you can be sure that the refactored version will preserve that functionality.

Full Code

Here's the full code with refactoring credit given to Brenda Bachtold. If you want to verify the doc tests, be sure to grab a copy of my DocTests routine here.

' ----------------------------------------------------------------
' Procedure : Dt
' Updated   : 2022-12-20  (v3)
' Author    : Mike Wolfe (refactored by Brenda Bachtold)
' Source    : https://nolongerset.com/dt-function-v3/
' 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#
Public Function Dt(d As Variant) As String
    Const Pound As String = "#"

    Select Case True
        'null or empty
        Case IsNull(d), IsEmpty(d)
           Dt = "Null"

        'time only
        Case Format(d, "yyyy-mm-dd") = "1899-12-30"
            Dt = Pound & Format(d, "hh:nn:ss") & Pound

        'date only
        Case Format(d, "hh:nn:ss") = "00:00:00"
            Dt = Pound & Format(d, "yyyy-mm-dd") & Pound

        Case Else
            Dt = Pound & Format(d, "yyyy-mm-dd hh:nn:ss") & Pound

    End Select
End Function

Referenced articles

Cover image created with Microsoft Designer