22,209
社区成员
发帖
与我相关
我的任务
分享
where CONVERT(varchar(10),A.MEA_DATETIME)+CONVERT(varchar(10),A.END_TIME)>=:B ANDCONVERT(varchar(10),A.MEA_DATETIME)+CONVERT(varchar(10),A.END_TIME)<=:C ';
where CONVERT(DATETIME,A.MEA_DATETIME)+CONVERT(DATETIME,A.END_TIME)>=:B AND CONVERT(DATETIME,A.MEA_DATETIME)+CONVERT(DATETIME,A.END_TIME)<=:C
SELECT CONVERT(VARCHAR(10),GETDATE())+'09:00'--不满足日期格式
/*
(无列名)
03 17 201709:00
*/
--转换时报错
SELECT CAST(CONVERT(VARCHAR(10),GETDATE())+'09:00' AS DATETIME)
--成功例子
SELECT CAST(CONVERT(VARCHAR(11),GETDATE(),120)+'09:00' AS DATETIME)
CONVERT(DATETIME,A.MEA_DATETIME)+CONVERT(DATETIME,A.END_TIME)>=:B AND CONVERT(DATETIME,A.MEA_DATETIME)+CONVERT(DATETIME,A.END_TIME)<=:C '
用這樣子報錯。字符串轉時間有問題。120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
[/quote] SELECT TOP 1 CONVERT(varchar(10),A.MEA_DATETIME)+CONVERT(varchar(10),A.END_TIME),*
FROM IPQC_MEASURING_H A
SELECT TOP 1 CONVERT(varchar(10),A.MEA_DATETIME,120)+CONVERT(varchar(10),A.END_TIME,120),*
FROM IPQC_MEASURING_H A
120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
if r1.Checked then
begin
sqls :='select a.*,isnull(b.g_r3,'''')+ '' '' + isnull(b.p_r3,'''')+'' ''+isnull(b.I_r3,'''') as aod,convert(int,b.qty) as qty,b.*,ado_aod= case (isnull(b.g_r3,'''')+ '''' + isnull(b.p_r3,'''')+''''+isnull(b.I_r3,''''))'+
' when '''' then '''' else ''AOD'' end,convert (varchar(10),a.mea_datetime)+'' ''+convert(varchar(10),a.start_time )as time ,copy_max_no ,B.* from IPQC_MEASURING_H AS A with(nolock) inner join IPQC_MEASURING_DD AS B with(nolock) '+
' on A.max_no=B.max_no '+
' where CONVERT(varchar(10),A.MEA_DATETIME,120)+CONVERT(varchar(10),A.END_TIME,120)>=:B AND CONVERT(varchar(10),A.MEA_DATETIME,120)+CONVERT(varchar(10),A.END_TIME,120)<=:C ';
if trim(edit3.Text)<>'' then
BEGIN
sqls :=sqls+' and a.cust_part_id=:G ';
END;
if trim(edit7.Text)<>'' then
BEGIN
sqls := sqls+' and A.ijo_no LIKE :A ';
END;
if trim(edit5.Text)<>'' then
BEGIN
sqls := sqls+ ' and a.maching_no=:M ';
END;
sqls := sqls +' order by a.Create_Date desc ';
ShowMessage(sqls);
// ShowMessage(formatdatetime('yyyy-mm-dd hh:mm:ss',dxdateedit1.Date + dxtimeedit1.Time));
// ShowMessage(formatdatetime('yyyy-mm-dd hh:mm:ss',dxdateedit2.Date + dxtimeedit2.Time));
WITH IPQC_MEASURING_H DO
BEGIN
close;
sql.Clear;
sql.Add(sqls);
parameters.ParamByName('B').Value := formatdatetime('yyyy-mm-dd hh:mm:ss',dxdateedit1.Date + dxtimeedit1.Time);
parameters.ParamByName('c').Value := formatdatetime('yyyy-mm-dd hh:mm:ss',dxdateedit2.Date + dxtimeedit2.Time);
if trim(edit7.Text)<>'' then
parameters.ParamByName('A').Value := trim(edit7.Text)+'%';
if trim(edit5.Text)<>'' then
parameters.ParamByName('M').Value := trim(edit5.Text);
if trim(edit3.Text)<>'' then
parameters.ParamByName('G').Value := trim(edit3.Text);
open();
label2.Caption := '癘魁计 '+inttostr(IPQC_MEASURING_H.RecordCount) +' 兵';
END;
有一個選擇查詢條件的,需要選擇日期和時間段的。
WITH IPQC_MEASURING_H DO
BEGIN
close;
sql.Clear;
sql.Add(sqls);
parameters.ParamByName('B').Value := formatdatetime('yyyy-mm-dd hh:mm:ss',dxdateedit1.Date + dxtimeedit1.Time);
parameters.ParamByName('c').Value := formatdatetime('yyyy-mm-dd hh:mm:ss',dxdateedit2.Date + dxtimeedit2.Time);
if trim(edit7.Text)<>'' then
parameters.ParamByName('A').Value := trim(edit7.Text)+'%';
if trim(edit5.Text)<>'' then
parameters.ParamByName('M').Value := trim(edit5.Text);
if trim(edit3.Text)<>'' then
parameters.ParamByName('G').Value := trim(edit3.Text);
where CONVERT(varchar(10),A.MEA_DATETIME,120)+CONVERT(varchar(10),A.END_TIME,120)>=:B AND CONVERT(varchar(10),A.MEA_DATETIME,120)+CONVERT(varchar(10),A.END_TIME,120)<=:C ';
修改為這樣也不行。還是需要選擇大一天才行。SELECT CONVERT(VARCHAR(10),GETDATE(),120)
CONVERT(DATETIME,A.MEA_DATETIME)+CONVERT(DATETIME,A.END_TIME)>=:B
CONVERT(DATETIME,A.MEA_DATETIME,120)+CONVERT(DATETIME,A.END_TIME,114)>=:B
parameters.ParamByName('B').Value := dxdateedit1.Date + dxtimeedit1.Time;
parameters.ParamByName('B').DataType := ftDateTime;