Date Format Issue (1 Viewer)

George-Bowyer

Registered User.
Local time
Today, 23:35
Joined
Dec 21, 2012
Messages
177
I live in the UK and use dd/mm/yyyy

I have the following code to select records for that fall within a financial year (1st May - 30th April)
Code:
        Case 2
        
            datStartDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date), True, Year(Date) - 1), 4, 30)
            datEndDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date) + 1, True, Year(Date)), 5, 1)
            datStartDate1 = Format(datStartDate1, "dd-mmm-yyyy")
            datEndDate1 = Format(datEndDate1, "dd-mmm-yyyy")
            
            MsgBox datStartDate1 & " " & datEndDate1
        
            strSQL = "SELECT Blah blah_
            " WHERE (((tblPaymentsDue.fldDateDue)>#" & datStartDate1 & "# And (tblPaymentsDue.fldDateDue)<#" & datEndDate1 & "#))" & _
            " ORDER BY tblPaymentsDue.fldDateDue, tblOrganisations.FldOrgName;"

The problem is that my SQL interprets 01/05/2019 as 5th Jan not 1st May.

I have tried formatting the dates to dd-mmm-yyyy as you can see, but whilst debug.print shows 01 May 2019 in the immediate window, it and the msgbox still show 01/05/2019 when the code is run for real and the SQL still interprets it as 5th Jan.

Can anyone help, please?

Thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:35
Joined
Sep 21, 2011
Messages
14,048
For SQL I always format mm/dd/yyyy

I also use this format a fair bit

Code:
Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

HTH
 
Last edited:

plog

Banishment Pending
Local time
Today, 18:35
Joined
May 11, 2011
Messages
11,613
I find that a custom function that takes a date and returns the fiscal year it lands it is the most effecient way to accomplish this:

FiscalYear: get_FiscalYear([YourDateField])

Then to compare 2 dates:

WHERE get_FiscalYear([Date1Field])=get_FiscalYear([Date2Field])

Better, that function consolidates the code in one place. So instead of putting that code everywhere to determine fiscal year, you just call that function from wherever you are (in queries, on forms, on reports).
 

Cronk

Registered User.
Local time
Tomorrow, 10:35
Joined
Jul 4, 2013
Messages
2,770
To elaborate on the previous response, the SQL interpreter is USA-centric and expects date in the format mm/dd/yyyy


Modify your code to

Code:
dteStartDate = format(dteStartDate,"mm/dd/yyyy")
.....
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:35
Joined
Sep 21, 2011
Messages
14,048
Can you even change the type of variable?

I tried the following. Try it for yourself

Code:
Sub testType()
Dim dtStart As Date, strStart As String

dtStart = DateSerial(2018, 11, 22)
Debug.Print dtStart

dtStart = Format(dtStart, "dd-mmm-yyyy")
Debug.Print dtStart

strStart = Format(dtStart, "dd-mmm-yyyy")
Debug.Print strStart

End Sub
 

George-Bowyer

Registered User.
Local time
Today, 23:35
Joined
Dec 21, 2012
Messages
177
To elaborate on the previous response, the SQL interpreter is USA-centric and expects date in the format mm/dd/yyyy


Modify your code to

Code:
dteStartDate = format(dteStartDate,"mm/dd/yyyy")
.....

Thanks. This works, in that it now returns the date spread that I want.

However, although I put the same format on datStartDate and datEndDate, the msgbox returns 30/04/2019 (UK format) on datStartDate and 05/01/2020 (US format) on datEndDate

It works, but it sort of still doesn't, but it does...
 

George-Bowyer

Registered User.
Local time
Today, 23:35
Joined
Dec 21, 2012
Messages
177
Can you even change the type of variable?

I tried the following. Try it for yourself

I tried this:


Code:
Sub testType()
Dim dtStart As Date, strStart As String

dtStart = DateSerial(2018, 11, 22)
Debug.Print dtStart

dtStart = Format(dtStart, "dd-mmm-yyyy")
Debug.Print dtStart

strStart = Format(dtStart, "dd-mmm-yyyy")
Debug.Print strStart

dtStart = Format(dtStart, "mm/dd/yyyy")
Debug.Print dtStart

End Sub

and got this in the immediate window:

22/11/2018
22/11/2018
22-Nov-2018
22/11/2018

The attempt to format US style is overridden.

As you can see from my reply to Cronk above, the results in the message box when running the code for real are even more screwed up (but it does seem to work).
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:35
Joined
May 21, 2018
Messages
8,463
The attempt to format US style is overridden
No it is not, that is not what is happening
Code:
dtStart = Format(dtStart, "mm/dd/yyyy")
The dtStart is being casted into a string and then casted back into a date. It is being displayed in your regional settings.
All dates are stored the exact same way in every regional settings
Days since 31Dec1899.FractionOfADay

It is being formatted in the specified settings, but just being casted back into a date.
 

George-Bowyer

Registered User.
Local time
Today, 23:35
Joined
Dec 21, 2012
Messages
177
Ok. Thanks. I think I get that.

However, I still don't understand why:

Code:
            datStartDate1 = Format(datStartDate1, "mm/dd/yyyy")
            datEndDate1 = Format(datEndDate1, "mm/dd/yyyy")
            
            MsgBox datStartDate1 & " " & datEndDate1

returns "30/04/2019 05/01/2020"

(1st in UK format, 2nd in US format)

I'm not complaining - because the code now works, but it seems odd...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:35
Joined
May 21, 2018
Messages
8,463
Code:
(1st in UK format, 2nd in US format)
Again, that is not what is happening. The second date is not displayed in US format, it has been converted to 5 Jan 2020 and being displayed in your settings. You are taking a date, explicitly converting to a string, and then implicitly casting it back to a date. I think if the month is 12 or below the conversion is as follows.
"5/12/2020" will get cast as dec 5
where
"5/13/2020" will get cast as may 13.
(I might have that reversed)
There is no reason you should be casting back and forth like this. Only in a SQL convert the literal date to "#mm/dd/yyyy#" ALWAYS regardless of your region.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:35
Joined
May 21, 2018
Messages
8,463
VBA will try to take its best guess.
Code:
datStartDate1 = Format(datStartDate1, "mm/dd/yyyy")
If datStartDate1 is declared as a date
Code:
 Format(datStartDate1, "mm/dd/yyyy")
returns a string
but
Code:
 datStartDate1 = SomeDateFormattedasAstring
will automatically convert that string into a date the best it can. Depending on the regional settings it may not convert it as you expect.
 

George-Bowyer

Registered User.
Local time
Today, 23:35
Joined
Dec 21, 2012
Messages
177
Ok, so I guess I shouldn't be using Format if that's converting the date to a string (yes, datEndDate is declared as a date - I wasn't even aware that you could format a declared date variable into a string; I thought the whole point of declaring a specific variable type was that it would only accept that type of variable?)

The question is: in that case, what should I do to the dates that I get from my DateSerial code (see top) to make them acceptable to SQL.

I use that dateserial code to derive dates in several places; as default values in controls or writing into recordsets and it has always accepted 01/05/YYYY quite happily as 1st May.

It's only where I am, for the first time, trying to use it as a criteria in SQL that I'm getting this date format issue.

If format("mm/dd/yyyy") is the not the best way to do it, even though it works for some reason, how should I approach it instead?
 

isladogs

MVP / VIP
Local time
Today, 23:35
Joined
Jan 14, 2017
Messages
18,186
I think you are still confusing yourself. To reinforce what's already been said, outside the US it is essential to explicitly format dates as mm/dd/yyyy for use in SQL expressions
If you don't do that, Access will swop over the dates up to the 12th of each month but will correctly interpret dates which aren't ambiguous (13th onwards). Unfortunately, by trying to be helpful, Access just adds to the confusion

Your example using 22 Nov 2018 wasn't very meaningful for two reasons:
Its unambiguous because its 22nd of the month AND because you used DateSerial

Instead try something like this
Code:
Sub TestDates()
Dim dtStart As Date, strStart As String

dtStart = #7/11/2018#  ' 7 Nov 2018 in the UK
Debug.Print dtStart

dtStart = Format(dtStart, "dd-mmm-yyyy")
Debug.Print dtStart

strStart = Format(dtStart, "dd-mmm-yyyy")
Debug.Print strStart

dtStart = Format(dtStart, "mm/dd/yyyy")
Debug.Print dtStart

End Sub

Results:
11/07/2018
11/07/2018
11-Jul-2018
07/11/2018

Only the last one is what you actually want

For another example, type an impossible date like 29 Feb 2019 in the immediate window and look at Access trying to be helpful. For example:
?#29/2/19#
19/02/2029
Aren't dates fun :rolleyes:
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:35
Joined
Feb 19, 2013
Messages
16,553
it is essential to explicitly format dates as mm/dd/yyyy for use in SQL expressions
FYI the format 'yyyy-mm-dd' will also work with SQL. Something I only learned a few months ago, but when viewed, easier to read than mm/dd/yyyy which does not come naturally to UK eyes
 

isladogs

MVP / VIP
Local time
Today, 23:35
Joined
Jan 14, 2017
Messages
18,186
Hi CJ
You are of course correct and I was already aware of that.
However I should have mentioned that alternative for the benefit of the OP.
 

George-Bowyer

Registered User.
Local time
Today, 23:35
Joined
Dec 21, 2012
Messages
177
I think you are still confusing yourself.

Oh, yes.

Your example using 22 Nov 2018 wasn't very meaningful for two reasons:
Its unambiguous because its 22nd of the month AND because you used DateSerial

It wasn't my example, it was Gasman's. I was replying.

Aren't dates fun :rolleyes:

No. No. No. :banghead:


To reinforce what's already been said, outside the US it is essential to explicitly format dates as mm/dd/yyyy for use in SQL expressions

Yes. That is what I want to know how to do?

I am currently using this:

Code:
            datStartDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date), True, Year(Date) - 1), 4, 30)
            datEndDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date) + 1, True, Year(Date)), 5, 1)
            datStartDate1 = Format(datStartDate1, "mm/dd/yyyy")
            datEndDate1 = Format(datEndDate1, "mm/dd/yyyy")
            
            strSQL = "SELECT blah blah _
            " FROM blah blah
            " WHERE (((tblPaymentsDue.fldDateDue)>#" & datStartDate1 & "# And (tblPaymentsDue.fldDateDue)<#" & datEndDate1 & "#))" & _
            " ORDER BY Blah

It works - but is it the correct/best way to do it?

(I ask because MajP tells me that I should not need "to cast back and forth like this" which implies that there's a better way.)

If not this, what should I do instead?



(I'm going to give up asking why the two date variables that have been through the same format procedure are then debug.printed in different formats, because plainly no one else appears to think that it's odd...)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 23:35
Joined
Feb 19, 2013
Messages
16,553
try

" WHERE (((tblPaymentsDue.fldDateDue)>#" & format(datStartDate1,"mm/dd/yyyy") & "# And (tblPaymentsDue.fldDateDue)<#" & format(datEndDate1,"mm/dd/yyyy") & "#))" & _
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:35
Joined
May 21, 2018
Messages
8,463
I ask because MajP tells me that I should not need "to cast back and forth like this" which implies that there's a better way.

Sorry I was not clear. When working in SQL you absolutely need to convert to MM/DD/YYYY format as been said numerous times.

But in your post you were doing this and expecting to see results in the immediate window in US format. That is not going to happen. If you format a date in US format into a string, then convert it back into a date, then print it. It is going to appear in your regions format as whatever date it was cast to.

You keep casting a string into date. You cannot do that
Code:
            datStartDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date), True, Year(Date) - 1), 4, 30)
            datEndDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date) + 1, True, Year(Date)), 5, 1)
            datStartDate1 = Format(datStartDate1, "mm/dd/yyyy")
            datEndDate1 = Format(datEndDate1, "mm/dd/yyyy")
You need a string variable
Code:
 dim strEndDate1 as string
  dim strStartDate1 as string
  .....
  datStartDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date), True, Year(Date) - 1), 4, 30)
  datEndDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date) + 1, True, Year(Date)), 5, 1)
  strStartDate1 = Format(datStartDate1, "mm/dd/yyyy") 'stop casting it back into a date
  strEndDate1 = Format(datEndDate1, "mm/dd/yyyy")
  'use this in the sql
 

isladogs

MVP / VIP
Local time
Today, 23:35
Joined
Jan 14, 2017
Messages
18,186
(I'm going to give up asking why the two date variables that have been through the same format procedure are then debug.printed in different formats, because plainly no one else appears to think that it's odd...)

LOL - the idea was just to show you that the same date can be displayed in many different ways.

I'm going to expand on CJL's answer & hopefully it will all make sense to you.
Here's your code as a procedure with added debug lines:

Code:
Sub TestDateSQL_v1()

Dim datStartDate1 As Date, datEndDate1 As Date, strSQL As String
 
datStartDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date), True, Year(Date) - 1), 4, 30)
datEndDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date) + 1, True, Year(Date)), 5, 1)
Debug.Print "1. " & datStartDate1, datEndDate1

datStartDate1 = Format(datStartDate1, "mm/dd/yyyy")
datEndDate1 = Format(datEndDate1, "mm/dd/yyyy")
Debug.Print "2. " & datStartDate1, datEndDate1
            
strSQL = "SELECT blah blah" & _
        " FROM blah blah" & _
        " WHERE (((tblPaymentsDue.fldDateDue)>#" & datStartDate1 & "# And (tblPaymentsDue.fldDateDue)<#" & datEndDate1 & "#))" & _
        " ORDER BY Blah;"
        
Debug.Print strSQL

End Sub

Here in the UK that gives:
1. 30/04/2018 01/05/2019
2. 30/04/2018 05/01/2019
SELECT blah blah FROM blah blah WHERE (((tblPaymentsDue.fldDateDue)>#30/04/2018# And (tblPaymentsDue.fldDateDue)<#05/01/2019#)) ORDER BY Blah;

As you can see:
1. Both dates are correct in step 1 (DateSerial)
2. The second date has been reversed in 2.
3. Similarly for the SQL statement ...which is of course wrong!

Lets try again, this time moving the formatting into the SQL statement as done by CJL

Code:
Sub TestDateSQL_v2()

Dim datStartDate1 As Date, datEndDate1 As Date, strSQL As String
 
datStartDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date), True, Year(Date) - 1), 4, 30)
datEndDate1 = DateSerial(Switch(Month(Date) > 4, Year(Date) + 1, True, Year(Date)), 5, 1)
Debug.Print "1. " & datStartDate1, datEndDate1
            
strSQL = "SELECT blah blah" & _
        " FROM blah blah" & _
        " WHERE (((tblPaymentsDue.fldDateDue)>#" & Format(datStartDate1, "mm/dd/yyyy") & "# And (tblPaymentsDue.fldDateDue)<#" & Format(datEndDate1, "mm/dd/yyyy") & "#))" & _
        " ORDER BY Blah;"
        
Debug.Print strSQL

End Sub

Results:
1. 30/04/2018 01/05/2019
SELECT blah blah FROM blah blah WHERE (((tblPaymentsDue.fldDateDue)>#04/30/2018# And (tblPaymentsDue.fldDateDue)<#05/01/2019#)) ORDER BY Blah;

Here's the SQL correctly shows both dates in mm/dd/yyyy format & will process the statement correctly

Just one more thing - you can shorten the WHERE filter by using BETWEEN...AND

Code:
" WHERE (((tblPaymentsDue.fldDateDue) Between  #" & Format(datStartDate1, "mm/dd/yyyy") & "# And #" & Format(datEndDate1, "mm/dd/yyyy") & "#))" & _

In case it helps, I have an example database where you can 'play with' date filtering: https://www.access-programmers.co.uk/forums/showthread.php?t=302189
There are 2 examples in that thread. The one you want is the Incident Analysis example
 

Users who are viewing this thread

Top Bottom