UK dates in SQL queries

[RogerC]

I have had a lot of problems trying to use a date to select a range of records from my database. Despite numerous tries at formatting the date to use in the SQL query, it has not worked, and the US date format has often selected records when I don't want them. For example when I ask for records after the 01/10/2018, the query returns everything after the 10th January 2018.
My solution is as follows:
I ask for the date I want to report from (NewMemberDate)

NewMemberDate = InputBox("Enter start of month to check for new members.", "New Members", "01/XX/2018")
'Then check to see if the UK month is less than 12

If Mid(NewMemberDate, 4, 2) < 13 Then
' convert the date to US date format
NewMemberDate = Mid(NewMemberDate, 4, 2) & "/" & Left(NewMemberDate, 3) & Right(NewMemberDate, 4)
End If


The NewMemberDate can then be passed into the SQL string by:
WHERE ([Members].[Commence] >= #' & NewMemberDate & '#)


If anyone knows of a more effective way of doing this then please let me know.

 

[Insane_ai]

You may want to review this:
Bad Habits to kick mis handling data range queries:
https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries

After digesting this I use only the universal standard with this code snippet in VBA:
(This is for passing date values to SQL Server)

Public Function GET_PTQ_Date(datDate As Date) As String
    'Format a date to universal SQL Date Format YYYYMMDD
    'initially intended for use in Pass-Through Queries
    GET_PTQ_Date = DatePart("yyyy", datDate) & Format(datDate, "mm") & Format(datDate, "dd")

End Function
[\CODE]

You can leverage similar functionality to extract the date parts and arrange them for your local settings.
[Code]
MyNewDate = Format(DatePart("dd",NewMemberDate)& "/" & (DatePart("mm",NewMemberDate) & "/" & DatePart("yyyy",NewMemberDate),"Short Date")


NewMemberDate = MyNewDate

Now you don't need to calculate the input, you can just take what you want and arrange it the way you need it. Feel free to adjust the above to suit your needs.

 

[Minty]

You can easily overcome most of these problems by using a form to control the user input.

If the query is saved and you refer to the form directly in the criteria of the query Access will normally use your windows locale setting correctly and handle this automatically.

If you are building dynamic queries in VBA then the date has to be input in either mm/dd/yyyy format or even more robustly yyyy/mm/dd format. You can easily use the function provided by Allen Browne for this ;

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd\#")
        Else
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd hh\:nn\:ss\#")
        End If
    End If
End Function

 

[isladogs]

Whilst you are perfectly correct that Access will correctly read UK formatted dates such as 13/11/2018, there is no point converting only dates less than 13th of each month to mm/dd/yyyy.

Instead convert all dates in sql statements using Format([DateField],"mm/dd/yyyy") or another similar expression.

It's not necessary in a query where Access interprets all dates correctly

Don't forget that Format will output a text string rather than a date.
If necessary wrap that expression in the CDate function to convert back to a date

 

[jdraw]

RogerC,

What was your solution? It may help someone else.
Sometimes asking user to select from a combo with Month names spelled out removes the US/UK/CAN date format confusion.

OOooops: I should have refreshed the thread before typing, again.