Special DBMS-dependent delimiters are used for querying date fields. If you don't use a delimiter, or use an incorrect one, you will get an error message.
The following query examples will select a date in a layer based on Microsoft Access personal geodatabase data:
[DATE_FIELD] = #1970/8/17#
[DATE_FIELD] = DateSerial(1970,8,17)
Coverages, shapefiles and SDE geodatabases use a different standard:
"DATE_FIELD" = date '1970/8/17'
You should use native DBMS date formats when working with SDE geodatabases.
For more information, click the Help button in the Select Menu, Select by Attributes choice.