Date format (1 Viewer)

kirkm

Registered User.
Local time
Today, 22:52
Joined
Oct 30, 2008
Messages
1,257
I thought I had Dates in Access figured out but this has floored me!
I understood dates in SQL have to be #MM/DD/YYYY# (and single M or D is also okay).

This function swaps Day and Month to suit SQL and has worked fine until a Date of 17 Oct.
Code:
Private Function USDate(d) As Date
Dim c
c = Split(d, "/")
USDate = CDate(c(1) & "/" & c(0) & "/" & c(2))
End Function
If d is 17/10/1987, USDate stays 17/10/1987 as if the split and reassignment are completely ignored.
Then, the non_US date returns a correct result from the SQL query.

Is it because 17 is > 12 and so must be the Day? Meaning it is NOT true that sql must be formatted #MM/DD/YYYY# ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:52
Joined
Oct 29, 2018
Messages
21,357
Hi. What is the data type of the argument d? What is the data type of the value you're passing to it?
 

kirkm

Registered User.
Local time
Today, 22:52
Joined
Oct 30, 2008
Messages
1,257
Hi, it's a Date datatype from a Recordset
Select ... from table1 where [Date Entered]= #" & USDate(Backend![Date Entered]) & "#"...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:52
Joined
Oct 29, 2018
Messages
21,357
Hi, it's a Date datatype from a Recordset
Select ... from table1 where [Date Entered]= #" & USDate(Backend![Date Entered]) & "#"...
Hi. Try changing your SELECT statement to this.
SQL:
"SELECT...FROM...WHERE [Date Entered]=#" & Format(Backend![Date Entered], "yyyy-mm-dd") & "#"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:52
Joined
May 7, 2009
Messages
19,169
be be exactly sure, test what Locale short time format is in use.
then work on from there.
add this to a Module:
Code:
#If VBA7 Then
Private Declare PtrSafe Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal LCID As Long, ByVal LCType As Long, lpData As Any, ByVal cchData As Integer) As Integer
Private Declare PtrSafe Function GetSystemDefaultLCID Lib "kernel32" () As Long
Private Declare PtrSafe Function GetUserDefaultLCID Lib "kernel32" () As Long
#Else
Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal LCID As Long, ByVal LCType As Long, lpData As Any, ByVal cchData As Integer) As Integer
Private Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long
Private Declare Function GetUserDefaultLCID Lib "kernel32" () As Long
#End If
Public Const LOCALE_SSHORTDATE = &H1F

Public Function StGetLocaleInfo(LCType As Long, Optional fUserDefault As Boolean = True) As String
    Dim LCID As Long
    Dim stBuff As String * 255
    
    'Get current language ID
    If fUserDefault Then
        LCID = GetUserDefaultLCID()
    Else
        LCID = GetSystemDefaultLCID()
    End If
    
    'ask for the locale info
    If (GetLocaleInfo(LCID, LCType, ByVal stBuff, Len(stBuff)) > 0) Then
        StGetLocaleInfo = StFromSz(stBuff)
    End If

End Function

your function:

Code:
Private Function USDate(d) As String
dim s As String
dim v As Variant
d=d & ""
s=StGetLocaleInfo(LOCALE_SSHORTDATE)
'we'll just assume we are looking for "m" or "d" as the first letter
if left(s,1)="m" then
    'no worry, already in US date format
elseif left(s, 1)="d" then
    'british format?
    d=replace(replace(d,".","/"),"-","/")
    v=split(d,"/")
    d=v(1) & "/" & v(0) & "/" & v(2)
end if
USDate = d
End Function
 

kirkm

Registered User.
Local time
Today, 22:52
Joined
Oct 30, 2008
Messages
1,257
Thanks. That's a new way of doing it and confirms my belief about USDate format in SQL is wrong. . Would "yyyy-mm-dd" also resolve the problem of Date matching whatever the regional settings are ?
 

kirkm

Registered User.
Local time
Today, 22:52
Joined
Oct 30, 2008
Messages
1,257
arnelgp, I ran into Function not defined on StFromSz.
Using Access 2010 here.
 

kirkm

Registered User.
Local time
Today, 22:52
Joined
Oct 30, 2008
Messages
1,257
dbGuy, sometimes I need to alter the date, so would you agree this is okay:
sql = Replace(sql, Format(Backend![Date Entered], "yyyy-mm-dd"), Format(#1/18/1987#, "yyyy-mm-dd"))
I'm not totally sure is this covers all contingencies? What does it do with #1/1/1987#
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:52
Joined
Oct 29, 2018
Messages
21,357
Thanks. That's a new way of doing it and confirms my belief about USDate format in SQL is wrong. . Would "yyyy-mm-dd" also resolve the problem of Date matching whatever the regional settings are ?
Hi. In expressions and VBA code, dates are expected to be in either U.S. or ISO format.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:52
Joined
Oct 29, 2018
Messages
21,357
dbGuy, sometimes I need to alter the date, so would you agree this is okay:
sql = Replace(sql, Format(Backend![Date Entered], "yyyy-mm-dd"), Format(#1/18/1987#, "yyyy-mm-dd"))
I'm not totally sure is this covers all contingencies? What does it do with #1/1/1987#
I don't follow exactly what you're trying to do with that.
 

kirkm

Registered User.
Local time
Today, 22:52
Joined
Oct 30, 2008
Messages
1,257
In the sql statement it's replacing one date with another. (That's conditional, not in every case)
> In expressions and VBA code, dates are expected to be in either U.S. or ISO format.
What do you mean by Expressions / VBA Code as opposed to SQL, and ISO?
My example #1/1/1987# was bad, of course that's Jan 1 either way! I tried other dates and it seems the first number is the month unless
it's a day. And it knows that by >12 or not. Would you agree? But there remains a need to (as arne showed) for test what Locale short time format ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:52
Joined
May 7, 2009
Messages
19,169
here is the missing:
Code:
'------------------------------------------------------------
'   StFromSz
'
'    Find the first vbNullChar in a string, and return
'    everything prior to that character. Extremely
'    useful when combined with the Windows API function calls.
'------------------------------------------------------------
Private Function StFromSz(ByVal sz As String) As String
    
    Dim ich As Integer
    
    ich = InStr(sz, vbNullChar)
    Select Case ich
        ' It's best to put the most likely case first.
        Case Is > 1
            ' Found in the string, so return the portion
            ' up to the null character.
            StFromSz = Left$(sz, ich - 1)
        Case 0
            ' Not found at all, so just
            ' return the original value.
            StFromSz = sz
        Case 1
            ' Found at the first position, so return an empty string.
            StFromSz = vbNullString
    End Select
End Function
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:52
Joined
Jan 20, 2009
Messages
12,849
I thought I had Dates in Access figured out but this has floored me!
I understood dates in SQL have to be #MM/DD/YYYY# (and single M or D is also okay).

The forward slash used in a format string for a date may not result in what you expect if your regional date format uses something other than a forward slash as the date separator because the forward slash is a placeholder in this context and the local date separator will be inserted in its place..

To ensure the forward slash is always used it should be literally escaped with a backslash.
Code:
Format("MM\/DD\/YYYY", somedate)

Instead of concatenating the # delimiters you can include them too with this format string.
Code:
Format("\#MM\/DD\/YYYY\#", somedate)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:52
Joined
Jan 20, 2009
Messages
12,849
Is it because 17 is > 12 and so must be the Day? Meaning it is NOT true that sql must be formatted #MM/DD/YYYY# ?
Of course it is true if you want predictable results.

However Windows has a "feature" where it will quietly interpret a date other than in the specified regional format in any way it can.

My favourites are dates like "29/2/19" which will be interpreted as 19 February 1929 because 2019 was not a leap year.
 

kirkm

Registered User.
Local time
Today, 22:52
Joined
Oct 30, 2008
Messages
1,257
Thanks Galaxiom, I was just about to ask arne under what conditions the regional date format needed to be checked. I'm pretty sure I have Date sorted now and DBguys suggestion using Format is working with US and UK regional settings (although both using forward slash). Does arne's
StGetLocaleInfo function return the delimiter used? (I shoudl change it and test I guess).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:52
Joined
May 7, 2009
Messages
19,169
yes, it will interpret the date Correctly if it exceed 12.
otherwise it will get Confused:

1/2/2020, which is the month
2/1/2020, which is the month

this will confuse Access:

debug.print IsDate("3a1-2-3")

check it! and you'll be surprised!

debug.print CDate("3a1-2-3")
 

isladogs

MVP / VIP
Local time
Today, 09:52
Joined
Jan 14, 2017
Messages
18,186
Thanks for that example Arnel.
As i always use a 24hour clock, I hadn't seen that type of issue before. Similarly
Code:
?Cdate(#3p1-2-3#)
02/01/2003 15:00:00
 

kirkm

Registered User.
Local time
Today, 22:52
Joined
Oct 30, 2008
Messages
1,257
Hey, dbGuy, I was wondering why you advised date format "yyyy-mm-dd"? Is there something about that to distinguish it from say, "dd mm yyyy" so Access knows the day-month order ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:52
Joined
Oct 29, 2018
Messages
21,357
Hey, dbGuy, I was wondering why you advised date format "yyyy-mm-dd"? Is there something about that to distinguish it from say, "dd mm yyyy" so Access knows the day-month order ?
That's the ISO format, and it avoids ambiguity.
 

Users who are viewing this thread

Top Bottom