CODEWITHSUNDEEP
sqldatabasems-access
Asdwq Qwksf
Asdwq Qwksf

Reputation: 141

Access SQL Date Format

How can I retrieve a record based on a Date property? I'm trying:

WHERE Meetings.[MDate] = '16/12/2011'

which is the format I use but I get :

"Data type mismatch in criteria expression"

Problem solved: It should have been:

WHERE Meetings.[MDate] = 16/12/2011

No quotation marks.

Upvotes: 12

Views: 60152

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

Use the cast to DATETIME function, CDATE(), which will honour the machine's regional settings. That said, it still a good idea to use an unambiguous date format and the ISO 8601 format is a good one.

Also note that Access doesn't have a date data type: its sole temporal data type is DATETIME and, as its name suggests, always has a time element accurate to one second time granule, even if that time happens to be midnight. Therefore, it is a good idea to always include a time value to one second time granule in all DATETIME literals e.g. 

WHERE Meetings.MDate = CDATE('2011-12-16 00:00:00');

Another advantage to the above is that the Access UI will not attempt to reformat the DATETIME literal because it is held as a string.

Upvotes: 6

Ricardo Souza
Ricardo Souza

Reputation: 16446

For where clauses use

columnName = #mm/dd/yyyy#

Upvotes: 14

competent_tech
competent_tech

Reputation: 44921

You'll want to use the SQL date format: '#2011-12-16#'

Upvotes: 12

Related Questions