International dates in Microsoft Access and SQL Server

One area where it is very easy to get into a mess is with dates, especially as in the United Kingdom we choose to use a different system to that used by the people who wrote most of the software that we use every day.

Microsoft Access is very good at hiding this from you, which actually doesn’t help when it comes to untangling any problems.

As a reminder, default UK dates are day/month/year, whereas default American dates are month/day/year. Your regional settings as set on your PC will control the appearance of the date in the user interface, so you may see a correctly formatted UK date whenever you look in a table or on a form, but in reality it is stored in US format.

Demonstrate the issue for yourself

You can prove this by taking a simple query against a table – in this case I have some bookings, with a booking date, and I want to find the booking numbers that I processed on 25th November 2020, so I create a query to do that and include the date I want as the criteria, delimited by the # character as Access requires:

When I run this query, it returns the jobs that I expect, so all is good, and I have used a UK format date as the criteria, so what is the issue? Well, right-click in the Query Designer window and choose SQL View, and you will see the SQL version of the query that you have just designed:

SELECT tblBooking.BookingID, tblBooking.JobNumber, tblBooking.BookingDate
FROM tblBooking
WHERE (((tblBooking.BookingDate)=#11/25/2020#));

You will immediately see on the highlighted line that the date here has been converted into the “native” American format.

Why does it matter, if I can use UK dates in my queries?

Generally speaking, if you are using Access alone, with an Access database behind it, you won’t have much of an issue because Access is clever at translating the dates from your regional settings to the American format which it uses by default, although you often can’t see it.

However, if you are getting into the VBA coding of modules and functions to give your database more power, then you will have a problem because you are likely to be writing SQL in the code to perform inserts or updates, and to run queries against your data. In these cases, because you are essentially bypassing the “front-end” of Access, you are losing its ability to translate the dates for you, so you need to make sure that US format dates are used.

Fortunately, many experts have provided a way around it, and one that I use frequently is the function below, created by Allen Browne. He also goes into much more technical detail about these dates here should you need it.

An example of the use of this function would be this SQL statement that inserts the current date into an invoice table for the selected invoice:

DoCmd.RunSQL ("update tblInvoice set InvoiceDate = " + SQLDate(Date) + " where InvoiceID = " + Me.InvoiceID)

The function itself is here:

Function SQLDate(varDate As Variant) As String
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

I hope this has helped to clarify where dates can go bad! It seems like a bit of a minefield, but the basic rule is that if you are using code to manipulate your database, stick to US format dates.