How to format date in a SQL query?

Question

Hello I'm using MS JET OLEDB 4.0 on VBA (MS ACCESS)

SQL = "SELECT COUNT(Date) FROM [Orders] WHERE [Orders].[Date] BETWEEN " & "#" & StartDate & "#" & " AND " & "#" & EndDate & "#"

The [Date] is formatted like f dd/mm/yyyy (French) and the StartDate and EndDate are formatted like mm/dd/yyyy (Two date objects).

My belief is that BETWEEN only compares dates formatted mm/dd/yyyy, so is there a way like a function to convert the [Date] formatting into mm/dd/yyyy so the between function can compare correctly?

Edit: Using a string instead of the dates like follow:

StartDateFormatted = Format(StartDate,"dd/mm/yyyy")
EndDateFormatted = Format(EndDate,"dd/mm/yyyy")

So as to be sure of the startdate and enddate format. It doesn't work still.

I'm left to assume two things:

Either BETWEEN onlys compares mm/dd/yyyy formats
I have to use a function to format [Date] to mm/dd/yyyy

Thanks to @Maciej Los for answering my question.

Don't know if it helps but found a piece of doc about a convert function: i.e. Format (Now, “MM/dd/yyyy”) udemy.com/blog/vba-format-date — Robert
– Robert, Commented Jan 7, 2015 at 12:39
However, since the columns should be date fields, the format shouldn't have any effect on the comparison, or am I wrong!? Is it possible that [Orders].[Date] is of another type (maybe a text)? — Robert
– Robert, Commented Jan 7, 2015 at 12:44
I would do a "select getDate()" and watch the format returned then use that format in the query. Another thing is that he formatting of the dates is a localization setting for the database. — Fabooo
– Fabooo, Commented Jan 7, 2015 at 12:44
@MaciejLos, This is one of those just because you can doesn't mean you should things in my book. — Tony Hopkinson
– Tony Hopkinson, Commented Jan 7, 2015 at 16:43

i486 · Accepted Answer · 2015-01-07 13:00:19Z

4

Always use ISO date formatting YYYY-MM-DD and you will have no problems. Without formatting.

answered Jan 7, 2015 at 13:00

i486

6,5354 gold badges26 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maciej Los · Accepted Answer · 2015-01-07 13:01:08Z

1

Use Format function.

SQL = "SELECT COUNT(Date)" & vbcr & _
"FROM [Orders]" & vbcr & _
"WHERE [Orders].[Date] BETWEEN #Format(" & StartDate & "], 'MM/dd/yyyy')# AND #Format(" & EndDate & ",'MM/dd/yyyy')#"

edited Jan 7, 2015 at 13:01

answered Jan 7, 2015 at 12:48

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

2 Comments

Amen Jlili Over a year ago

I can't use that. VBA has " problems. Can you format it?

user2140173 Over a year ago

try between #" & Format([StartDate], "MM/dd/yyyy") & "# AND #" & Format([EndDate], "MM/dd/yyyy") & "#"

Stephen Rauch · Accepted Answer · 2017-08-31 06:33:03Z

1

I have noticed from the SQL compilation of the query when using Between, that it turns it to MM/DD/YYYY and it did not work till I used Format(startdate, "mm/dd/yyyy"). Please check the snapshop.

edited Aug 31, 2017 at 6:33

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

answered Aug 31, 2017 at 6:14

George Tomeh

132 bronze badges

Collectives™ on Stack Overflow

How to format date in a SQL query?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related