在MS-Access中使用Datetime插入SQL命令

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

我在MS-Access 2007中尝试以下查询,但它在时间字段上失败。

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.

TIME字段定义为日期时间。

Without the TIME field, the query works fine!

没有TIME字段,查询工作正常!

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!

感谢你们!这几乎让我完全在那里。我仍然一直得到insert语句的语法错误,但是在进一步的谷歌搜索中,我意识到TIME可能是一个保留的关键字,所以把它放在方括号上,因为[TIME]工作了!

1 个解决方案

#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)

访问使用#中的日期和时间输入,因为访问不能在SQL查询(或访问调用查询)中从字符/文本自动转换为日期或时间,并且您最好使用国际标准输入日期时间,即YYYY- MM-DD HH:NN:SS(4位数年份,2位数月份,2位数日期,2位数小时,2位数分钟,2位数秒)

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

所以对于4/21/2009 2:25:53 PM使用#2009-04-21 14:25:53#

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

或者如果它仍然失败,你可以使用#'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

编辑:如果您启用ANSI 92或使用ADO / OLEDB作为数据库接口,则上面可能正常工作,感谢David指出

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

我建议你使用YYYY-MM-DD HH:NN:SS格式并在使用前用单引号(')试试#就像我上面说的那样

#1