delphi日期格式化_在Delphi中格式化Access SQL的日期时间值

delphi日期格式化

Ever get the awful "Parameter object is improperly defined. Inconsistent or incomplete information was provided" JET error? Here's how to rectify the situation.

是否曾经遇到过可怕的“ 参数对象定义不正确。提供的信息不一致或不完整 ”的JET错误？ 这是纠正情况的方法。

When you need to create a SQL query against ​an Access database where a date (or a date time) value is used you need to make sure the correct formatting is used.

当您需要针对使用日期(或日期时间)值的Access数据库创建SQL查询时，需要确保使用正确的格式。

For example, in a SQL query: "SELECT * FROM TBL WHERE DateField = '10/12/2008'" you want to get all the records from the table named TBL where a general date field DateField equals 10/12/2008.

例如，在SQL查询中：“ SELECT * FROM TBL WHERE DateField = '10 / 12/2008'”，您想从名为TBL的表中获取所有记录，其中常规日期字段DateField等于10/12/2008。

Is the line above clear? Is that December, 10 or October, 12? Luckily, we are pretty sure the year in the query is 2008.

上面的线清楚了吗？ 是12月10日还是12月10日？ 幸运的是，我们可以肯定查询中的年份是2008年。

Should the date part of the query be specified as MM/DD/YYYY or DD/MM/YYYY or maybe YYYYMMDD? And do regional settings play a role here?

是否应将查询的日期部分指定为MM / DD / YYYY或DD / MM / YYYY或YYYYMMDD？ 区域设置在这里发挥作用吗？

MS Access，Jet，日期时间格式 ( MS Access, Jet, Date Time Formatting )

When using Access and JET (dbGo - ADO Delphi controls) the formatting of the SQL for the date field should *always* be:

使用Access和JET( dbGo-ADO Delphi控件 )时， 日期字段SQL格式应*始终*应为：

Anything else might work in limited testing but can often lead to unexpected results or errors on the user's machine.

其他任何事情都可能在有限的测试中起作用，但通常会导致意外结果或用户计算机上的错误。

Here's a custom Delphi function you can use to format a date value for the Access SQL query.

这是一个自定义的Delphi函数，可用于格式化Access SQL查询的日期值。

For "January 29, 1973" the function will return the string '#1973-01-29#'.

对于“ 1973年1月29日”，该函数将返回字符串“＃1973-01-29＃”。

访问SQL日期时间格式？ ( Access SQL Date Time Format? )

As for the date and time formatting, the general format is:

至于日期和时间格式，一般格式为：

This is: #year-month-daySPACEhour:minute:second#

这是：＃year-month-daySPACEhour：minute：second＃

As soon as you construct a valid date time string for the SQL using the above general format and try it using any of Delphi's dataset components as TADOQuery, you will receive the awful "Parameter object is improperly defined. Inconsistent or incomplete information was provided" error at run-time!

一旦使用上述常规格式为SQL构建有效的日期时间字符串，并使用Delphi的任何数据集组件作为TADOQuery对其进行尝试，您将收到可怕的“参数对象定义不正确。提供了不一致或不完整的信息”错误在运行时 ！

The problem with the format above is in the ":" character - as it is used for parameters in parametrized Delphi queries. As in "... WHERE DateField = :dateValue" - here "dateValue" is a parameter and the ":" is used to mark it.

上面格式的问题在于“：”字符-因为它用于参数化Delphi查询中的参数。 就像在“ ... WHERE DateField =：dateValue”中一样-这里“ dateValue”是一个参数，“：”用于标记它。

One way to "fix" the error is to use another format for date/time (replace ":" with "."):

“修复”错误的一种方法是对日期/时间使用另一种格式(用“。”替换“：”)：

And here's a custom Delphi function to return a string from a date time value you can use when constructing SQL queries for Access where you need to search for a date-time value:

这是一个自定义的Delphi函数，用于从日期时间值返回字符串，您可以在构造AccessSQL查询时使用该日期时间值来搜索日期时间值：

The format looks weird but will result in the correctly formatted date time string value to be used in SQL queries!

该格式看起来很怪异，但会导致在SQL查询中使用正确格式的日期时间字符串值！

Here's a shorter version using the FormatDateTime routine:

这是使用FormatDateTime例程的较短版本：

翻译自: https://www.thoughtco.com/formatting-date-time-values-access-sql-1057843

delphi日期格式化