时间戳

首页 > 解决方案 > 使用参数的 SQL 日期查询 - Delphi

sql - 使用参数的 SQL 日期查询 - Delphi

问题描述

我有一个 SQL 查询（MS Access），我想将 Date() 函数添加到一个参数中，但是我得到了错误：[ODBC Microsoft Access Driver]Data type mismatch in criteria expression. 这是代码：

Qry.SQL.Text := 'SELECT Bookings.Date, Bookings.WeekDay, Bookings.Shift, Bookings.Start, Bookings.Finish,'
    + ' Bookings.DateFinish, Wards.WardName'
    + ' FROM Bookings'
    + ' INNER JOIN Wards ON Bookings.WardNo = Wards.WardNo'
    + ' WHERE (Bookings.NurseNo=:nurseID) AND (Bookings.Date BETWEEN :dateA AND :dateB)'
    + ' ORDER BY Bookings.Date ASC;';

Qry.Params.ParamByName('dateA').Value := 'Date()';
Qry.Params.ParamByName('dateB').Value := 'Date()+6';

我也尝试过Qry.Params.ParamByName('dateA').AsString := 'Date()';，但没有运气，有没有正确的方法可以做到这一点，或者它实际上必须在查询中而不是参数化？我想这样做的原因是，我将根据按下的按钮有多个不同的查询，但唯一改变的是那些参数化的日期。

标签： sqldelphims-access

解决方案

A parameter can't be a function, it has to be a value. You are assigning strings as those values, and those strings do not represent valid dates. That is why you are getting a mismatch error.

You can use Delphi Date() function, and pass the returned TDate as a parameter value:

Qry.Params.ParamByName('dateA').Value := Date();
Qry.Params.ParamByName('dateB').Value := Date()+6;

Or, you can use Access's Date() function in the SQL itself:

Qry.SQL.Text := 'SELECT Bookings.Date, Bookings.WeekDay, Bookings.Shift, Bookings.Start, Bookings.Finish,'
    + ' Bookings.DateFinish, Wards.WardName'
    + ' FROM Bookings'
    + ' INNER JOIN Wards ON Bookings.WardNo = Wards.WardNo'
    + ' WHERE (Bookings.NurseNo=:nurseID) AND (Bookings.Date BETWEEN Date() AND Date() + 6)'
    + ' ORDER BY Bookings.Date ASC;';

推荐阅读