在MS-Access中将带有Datetime的SQL命令插入 [英] Insert SQL command with Datetime in MS-Access

问题描述

我正在尝试在MS-Access 2007中执行以下查询，但是在时间字段上失败.

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
)

TIME字段定义为日期时间.

The TIME field is defined as a datetime.

没有TIME字段，查询工作正常！

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.

谢谢你们！那几乎使我完全明白了.我仍然不断收到insert语句的语法错误，但是在进一步谷歌搜索后，我意识到TIME可能是保留关键字，因此在[TIME]起作用时将其放在方括号中！

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!

推荐答案

日期&在访问中输入的时间使用#，因为访问无法在SQL查询(或访问称为查询)中将字符/文本自动转换为日期或时间，因此最好使用国际标准输入日期 YYYY-MM-DD HH:NN:SS 的时间(4位数字的年份，2位数字的月份，2位数字的日期，2位数字的小时，2位数字的分钟，2位数字的秒)

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)

因此在 2009/4/21下午2:25:53 中使用#2009-04-21 14:25:53#

或者如果仍然失败，则可以使用#'2009-04-21 14:25:53'#

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

如果启用ANSI 92或使用ADO/OLEDB作为数据库接口，则上面的方法可能有效，谢谢David指出

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

我建议您使用YYYY-MM-DD HH:NN:SS格式，并在使用#之前尝试使用单引号(')，如我上面所述

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

这篇关于在MS-Access中将带有Datetime的SQL命令插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！