Insert SQL command with Datetime in MS-Access

Question

Hello,

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.

Any help would be much appreciated!

Thanks!!

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

Answer 2

Thank you guys! That almost got me fully there. I still kept getting the syntax errror 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!

Answer 3

what is sql command ?check acording to date and time command ?

Answer 4

what is mome basics command in soq(dbms)