Insert SQL command with Datetime in MS-Access

Question

I am trying the following query in MS-Access 2007, but it fails on the time field.

INSERT INTO LOG (
    EMPLOYEECODE, STATUSID, LOCATIONID, TIME, DURATION,
    SHIFTID, LATECOMING, EARLYGOING, LOGDATE, STATIONID
)
VALUES (
    1, 1, 0, '4/21/2009 2:25:53 PM', 0, 
    8, 0, 1, '1/1/2009', 1
)

The TIME field is defined as a datetime.

Without the TIME field, the query works fine!

I've tried a number of different things, such as enclosing the datetime in hashes, quotes etc. However, the query still fails on the time field.

---

Thank you guys! That almost got me fully there. I still kept getting the syntax error for the insert statement, but then on further googling, I realized that TIME might be a reserved keyword, so putting it on box brackets as [TIME] worked!

Corr

Answer 1

Date & Time input in access use #, since access can't do auto conversion from char/text into date or time in SQL Query (or access call it query), and you better use international standard for inputting date time which was YYYY-MM-DD HH:NN:SS (4-digit year, 2-digit month, 2-digit day, 2-digit hour, 2-digit minute, 2-digit second)

so for 4/21/2009 2:25:53 PM use #2009-04-21 14:25:53#

or if it still fail, you can use #'2009-04-21 14:25:53'#

Edit: Above might be working if you enable ANSI 92 or using ADO/OLEDB as database interface, thanks David for pointing out

I suggest you use YYYY-MM-DD HH:NN:SS format and try it with single quotes (') before use # like i said above

Hallerson

FWIW ISO date format within single quotes always works for me. – Pounce

What database interface are you using? ADO/OLEDB? If so, that would be correct. If you're working within Access, it won't unless you've set your options to use ANSI 92 by default. – Nazario

@David: yes usually i use ADO/OLEDB, but it works for me on Access Query too (maybe i've set it using ANSI 92???) – Hallerson

Yes, I (almost) always use OLE DB and always set the Access UI to ANSI-92 Query Mode. – Pounce

Thanks man! I finally understood how access stores DATE object – Serge

Only single quotes worked for me. For example: '2020-03-25 14:25:53' . Thanks a lot. – Proulx

This answer (with comments) is confusing. Never use quotes, always octothorpes. – Cavesson