从Excel导入数据到SQL，已经设置了单元格是日期值，但导入时还是出错。怎么解决呢？

Question

出错提示：
not convert variant of type (String) into type (Date)

Answer 1

也试过了，还是不行，StrToDateTime也试了，也不行。一样的错误。数据库里的字段类型是datetime  改成smalldatetime也不行

Answer 2

这个问题由于系统的时间各式的问题。由于系统的时间格式有很多（如：yyyy-MM-dd,MM-dd-yyyy），如果从字符简单的转换成时间的话，格式要和系统的时间个是一样，strTodate(YourDate)才不会出错。
这个问题本人是这样解决的，第一：可以在数据库中不用datetime类型。第二：如果真要用，自己写一个函数把字符转成时间的格式。再去做其它的运算。以str为（yyyy-MM-dd）为例。仅供参考。
function StrtoDateF(str: string): TDate;
begin
  StrtoDateF:= EncodeDate(strtoint(Copy(str,1,4)),strtoint(copy(str,6,2)),strtoint(copy(str,9,2)));
end;

Answer 3

把excel里的日期格式都改为文本格式！读出来只认文本的

Answer 4

和楼主同样的问题,超烦.跟踪时,strtodatetime这些根本不能用

Answer 5

代码终于改好，可以成功导入了。和原来出错的代码相比，我多定义了一个String类型数组。用数组接收单元格的值，再赋给ADOQuery的Parameters 连转换函数都不用了。而原来是直接读取单元格赋值给Parameters的。难道错误出在这吗？代码如下：procedure TfrmImportData.OpenDialog1CanClose(Sender: TObject;
  var CanClose: Boolean);    var sFileName:string;        iRepetendCount:integer;
        iStartRows:integer;
        iEndRows:integer;
        iParamIndex:integer;
        iArrayIndex:integer;        vExcel:Variant;
        vWorkBook:Variant;
        vWorkSheet:Variant;        aExcelFields:array[0..9] of string;
begin
   if MessageBox(self.Handle,'开始导入数据。点击[是]开始。','数据导入',64+MB_YESNO)=7 then
       Exit
   else
       sFileName:=OpenDialog1.FileName;       try
           vExcel:=CreateOleObject('Excel.Application');
           vWorkBook:=vExcel.WorkBooks.Open(sFileName);
           vWorkSheet:=vExcel.WorkBooks[1].WorkSheets[1];
       except
           vWorkBook:=UnAssigned;
           vExcel.Quit;
           Application.MessageBox('文件打开时出错。请确认你安装了Excel并且打开的是正确的文件类型。','错误',0+16);
           Exit;
       end;       iStartRows:=StrToInt(Edit1.Text);
       iEndRows:=StrToInt(Edit2.Text);       for iRepetendCount:=iStartRows to iEndRows do
       begin
           for iArrayIndex:=0 to 9 do
           begin
        aExcelFields[iArrayIndex]:=(vWorkSheet.Cells[iRepetendCount,iArrayIndex+1]);
           end;           try
               QImportData.Close;
               QImportData.SQL.Clear;
               QImportData.SQL.Add('insert into tbltest);
               QImportData.SQL.Add(' values (:a,:b,:c,:d,:e,:f,:g,:h,:i,:j)');               for iParamIndex:=0 to 9 do
               begin
               if aExcelFields[iParamIndex]='' then
                   QImportData.Parameters[iParamIndex].Value:=NULL
               else  
               QImportData.Parameters[iParamIndex].Value:=aExcelFields[iParamIndex];
               end;               QImportData.ExecSQL;
           except
               vWorkSheet:=UnAssigned;
               vWorkBook:=UnAssigned;
               vExcel.Quit;
               vExcel:=UnAssigned;
               Application.MessageBox('数据传输出错！即将中断导入。','警告',64+0);
               Exit;
           end;
       end;
       
       vWorkSheet:=UnAssigned;
       vWorkBook:=UnAssigned;
       vExcel.Quit;
       vExcel:=UnAssigned;
       Application.MessageBox('数据导入完成。','信息',0+64);
end;

Answer 6

漏了些代码：procedure TfrmImportData.OpenDialog1CanClose(Sender: TObject;
  var CanClose: Boolean);    var sFileName:string;        iRepetendCount:integer;
        iStartRows:integer;
        iEndRows:integer;
        iParamIndex:integer;
        iArrayIndex:integer;        vExcel:Variant;
        vWorkBook:Variant;
        vWorkSheet:Variant;        aExcelFields:array[0..9] of string;
begin
   if MessageBox(self.Handle,'开始导入数据。点击[是]开始。','数据导入',64+MB_YESNO)=7 then
       Exit
   else
       sFileName:=OpenDialog1.FileName;       try
           vExcel:=CreateOleObject('Excel.Application');
           vWorkBook:=vExcel.WorkBooks.Open(sFileName);
           vWorkSheet:=vExcel.WorkBooks[1].WorkSheets[1];
       except
           vWorkBook:=UnAssigned;
           vExcel.Quit;
           Application.MessageBox('文件打开时出错。请确认你安装了Excel并且打开的是正确的文件类型。','错误',0+16);
           Exit;
       end;       iStartRows:=StrToInt(Edit1.Text);
       iEndRows:=StrToInt(Edit2.Text);       for iRepetendCount:=iStartRows to iEndRows do
       begin
           for iArrayIndex:=0 to 9 do
           begin
        aExcelFields[iArrayIndex]:=(vWorkSheet.Cells[iRepetendCount,iArrayIndex+1]);
           end;           try
               QImportData.Close;
               QImportData.SQL.Clear;
               QImportData.SQL.Add('insert into tbltest);
               QImportData.SQL.Add(' values (:a,:b,:c,:d,:e,:f,:g,:h,:i,:j)');               for iParamIndex:=0 to 9 do
               begin
               if aExcelFields[iParamIndex]='' then
                       DataMod.QImportData.Parameters[iParamIndex].Value:=NULL
                   else if (iParamIndex=7)or(iParamIndex=8)or(iParamIndex=10) then
                            DataMod.QImportData.Parameters[iParamIndex].Value:=StrToDateTime(aExcelFields[iParamIndex])];{漏掉的 现在补上}
                        else
                            DataMod.QImportData.Parameters[iParamIndex].Value:=aExcelFields[iParamIndex
               end;               QImportData.ExecSQL;
           except
               vWorkSheet:=UnAssigned;
               vWorkBook:=UnAssigned;
               vExcel.Quit;
               vExcel:=UnAssigned;
               Application.MessageBox('数据传输出错！即将中断导入。','警告',64+0);
               Exit;
           end;
       end;
       
       vWorkSheet:=UnAssigned;
       vWorkBook:=UnAssigned;
       vExcel.Quit;
       vExcel:=UnAssigned;
       Application.MessageBox('数据导入完成。','信息',0+64);
end;