1

There are probably many questions that are asking about date formats, but I haven't found anything like this.

I have a table, called t_birthday. t_birthday has a field called "DayOfMonth" which currently stores the data in a dd/mm/yyyy format. Lets say the record I have has the Date of 01/12/2016 (Dec 1, 2016).

Now, if I create a query using the "Query Design" option in the Create tab, I select my table t_birthday. For the field option, I select DayOfMonth. In the criteria option, I put =#01/12/2016#. When I click Run, it queries the database and returns the record with that date successfully.

However.. If I check the SQL generated from this Query Design, it is this:

SELECT t_birthday.DayOfMonth
FROM t_birthday
WHERE (((t_birthday.DayOfMonth)=#12/1/2016#));

If I try copy and pasting the DayOfMonth value from the table into that query, it wouldn't work. Notice how the format in the query is mm/dd/yyyy, but in my table it's still dd/mm/yyyy. I never touched any of the date formatting options in my table, or even on my computer. When I actually create this record using a form, I have a date picker which is in the form of dd/mm/yyyy as well.

Questions:

  • In the query design, when I specify criteria in dd/mm/yyyy, why does it generate sql in the form of mm/dd/yyyy?

  • I can only query dates using dd/mm/yyyy format if the day number (1-31) is 13 or above, OR if the month value and the day value are the same (October 17, Jan 1, March 3, November 11, December 12, etc). mm/dd/yyyy still works for those dates previously mentioned. I can't query dates like November 7th, Feb 3rd, August 4th, etc using dd/mm/yyyy though. How do I get around this problem? I store the dates, and I use the values directly from the table as conditionals in my queries. I shouldn't have to alter my date value in order to use them.

  • Why can I write an SQL statement for dates with the day number above 13 in dd/mm/yyyy format or mm/dd/yyyy format? E.g., the WHERE clause can look like: WHERE DayOfMonth=#13/06/2018 or WHERE DayOfMonth=#06/13/2018 and it still returns the same record? Why does access not enforce a specific format?

EDIT:

Currently I run my query in VBA and return it into a recordset using the following:

Dim bdayRecords As RecordSet
Dim sql As String
sql = "SELECT t_birthday.DayOfMonth"
sql = sql & " FROM t_birthday"
sql = sql & " WHERE (((t_birthday.DayOfMonth)=#" & rs("DayOfMonth") & "#));"
bdayRecords = CurrentDb.OpenRecordset(sql)

Where rs in the where clause was a previous recordset with a date value stored in "DayOfMonth". The rs recordset retrieved the date value from a different table in the exact same way bdayRecords was populated.

bdayRecords won't find the records with the date values matching the criteria explained before.

Michael
  • 3,093
  • 7
  • 39
  • 83

2 Answers2

2

SQL always uses mm/dd/yyyy. That's not dependent on how you format it.

You never actually store a date in a certain format. You display a date in a certain format. All dates in Access are stored as a double-precision floating number containing the number of days elapsed since 30-12-1899, with fractions as time. How dates are formatted has no influence whatsoever on your SQL statement

Always use either mm/dd/yyyy or yyyy-mm-dd in your SQL. VBA only takes mm/dd/yyyy.

However, Access is opportunistic when working with clearly invalid dates, such as 13/1/2018. Because no 13th month exists, it parses it as the 13th of january, even though it's not a valid date.

If you're using values from other queries, there shouldn't be any problems, since the values never get cast back and forth to strings. You only get in trouble when casting a date to a string and then back to a date, which is not something you should do in queries, ever.

To avoid casting back and forth between strings, you can either refactor your code to a single query instead of retrieving a value from a recordset and inserting that value in a string SQL statement, or use parameters, which allows you to use the date value directly in an SQL statement.

For explanations why these design choices are made, ask Microsoft, they wrote the program. This is just how it works.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Please take a look at my EDIT section in my question. You'll see that I query a table, and use the value from that recordset in another queries WHERE clause. Is this the correct way to do this? – Michael Jun 20 '18 at 15:13
  • 1
    Use parameters, or refactor that code to use a single query instead of recordsets. You're casting back and forth to a string there, so as said, that's a problem. For how to use parameters, see [How do I use parameters in VBA in the different contexts in Microsoft Access?](https://stackoverflow.com/q/49509615/7296893). Using `Format` to format that date works too, but parameters are generally preferred over string concatenation. – Erik A Jun 20 '18 at 15:17
2

Use a properly formatted string expression for the date value retrieved:

sql = sql & " WHERE t_birthday.DayOfMonth = #" & Format(rs("DayOfMonth").Value, "yyyy\/mm\/dd") & "#;"

The ISO sequence yyyy-mm-dd works everywhere, so make it a habit to use that.

Gustav
  • 53,498
  • 7
  • 29
  • 55