erro SQL

[Antonio Maucyr Pasqualli F]

Ola amigos,

Em minha aplicação, "estou" com o seguinte erro:

Dynamic SQL error

SQL error code=303

conversion error from string "19/04/2009"

incompatible column/host variable data type

Error code: 249

segue link com a img do erro,

http://ube-167.pop.com.br/repositorio/9088/meusite/erro.JPG

estou aprendendo delphi, e estou com esse erro, alguém poderia me

ajudar?

Obrigado!

[William - TI]

caro amigo antes de passar a data como parametro da consulta faça assim

FormatDateTime('yyyy-mm-dd',date) //onde date é a data que você precisa isto deve resolver

esta função irá colocar a data em formato sql =2009-04-20;

caso tenha mais alguma duvida posta ai.

abraços.

[Antonio Maucyr Pasqualli F]

Vlw pela dica amigo,

bom, eu utilizo firebird 1.5

preciso realizar o controle de caixa, tem dois campos, um ele entra com a data inicial e outro com a tada final, então ele fica sabendo quanto esta "entrando", mas ele da o erro:

Dynamic SQL error

SQL error code=303

conversion error from string "19/04/2009"

incompatible column/host variable data type

Error code: 249

Segue o link com as imgs uma do erro e outra do aplicativo (parte dele onde ocorre o erro)

http://ube-167.pop.com.br/repositorio/9088/meusite/001.JPG

http://ube-167.pop.com.br/repositorio/9088/meusite/002.JPG

Agora o fonte de como estou fazendo:

-----------------------------------------------------------------------------------

unit ufluxo;

interface

uses

printers, mmsystem, Windows, variants, Messages, clipbrd, SysUtils, Classes,

Graphics, Controls, Forms, Dialogs,

ExtCtrls, Grids, DBGrids, ComCtrls, Db, DBTables, StdCtrls, DBCtrls, Mask,

Buttons, ToolWin, ActnList, Menus, QRCtrls, QuickRpt;

type

Tffluxo = class(TForm)

Panel1: TPanel;

DBGrid2: TDBGrid;

DBGrid3: TDBGrid;

Panel2: TPanel;

Panel3: TPanel;

Panel4: TPanel;

Label1: TLabel;

Bevel1: TBevel;

MaskEdit1: TMaskEdit;

MaskEdit2: TMaskEdit;

Label2: TLabel;

Label4: TLabel;

Bevel2: TBevel;

Bevel3: TBevel;

Bevel4: TBevel;

Bevel5: TBevel;

lsaldo: TLabel;

LBALCAO: TLabel;

Bevel6: TBevel;

LMESA: TLabel;

Label17: TLabel;

Bevel7: TBevel;

LSAIDA: TLabel;

ActionList1: TActionList;

somar: TAction;

Panel5: TPanel;

DBGrid1: TDBGrid;

Panel6: TPanel;

Bevel8: TBevel;

Bevel9: TBevel;

Label3: TLabel;

Label5: TLabel;

lentrega: TLabel;

DBGrid4: TDBGrid;

RgTipo: TRadioGroup;

Action1: TAction;

procedure MaskEdit1KeyDown(Sender: TObject; var Key: Word;

Shift: TShiftState);

procedure MaskEdit2KeyDown(Sender: TObject; var Key: Word;

Shift: TShiftState);

procedure FormCreate(Sender: TObject);

procedure somarExecute(Sender: TObject);

procedure FormClose(Sender: TObject; var Action: TCloseAction);

procedure RgTipoClick(Sender: TObject);

procedure Action1Execute(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

end;

var

ffluxo: Tffluxo;

implementation

uses udm, SqlExpr;

{$R *.dfm}

procedure Tffluxo.MaskEdit1KeyDown(Sender: TObject; var Key: Word;

Shift: TShiftState);

begin

if key = 13 then

maskedit2.SetFocus;

end;

procedure Tffluxo.MaskEdit2KeyDown(Sender: TObject; var Key: Word;

Shift: TShiftState);

begin

if key = 27 then maskedit1.SetFocus;

if key=13 then somarexecute(self);

end;

procedure Tffluxo.FormCreate(Sender: TObject);

begin

maskedit1.text := datetostr(now);

maskedit2.text := datetostr(now);

somarexecute(self);

end;

procedure Tffluxo.somarExecute(Sender: TObject);

var

st1, st2, cmd, cmdBalcao, tpag:string;

tot1, tot2, tot3, tot4 :real;

begin

if (maskedit1.Text <> ' / / ') and (maskedit2.Text <> ' / / ') then begin

st1:= maskedit1.Text;

st2:= maskedit2.Text;

case RgTipo.ItemIndex of

0: begin

cmd := 'select (data) as ENTRADA, sum(total)as VALOR from DLV$tbvenda'+

' where data between :Pst1 and :Pst2 group by data order by data';

cmdBalcao := 'select (data) as ENTRADA, sum(total)as VALOR from DLV$tbpedido1'+

' where data between :Ps1 and :Ps2 group by data order by data';

// mesas

with fdm.query1 do

begin

Close;

commandtext := cmd;

ParamByName('Pst1').AsString := st1;

ParamByName('Pst2').AsString := st2;

Open;

end;

// balcão

with fdm.query2 do

begin

Close;

commandtext := CmdBalcao;

ParamByName('Ps1').AsString := st1;

ParamByName('Ps2').AsString := st2;

Open;

end;

end;

1: begin

tpag := 'D';

cmd:='select (data) as ENTRADA, sum(total)as VALOR from DLV$tbvenda'+

' where data between :Pst1 and :Pst2 and TIPO= :Ppag group by data order by data';

cmdBalcao:= 'select (data) as ENTRADA, sum(total)as VALOR from DLV$tbpedido1'+

' where data between :Ps1 and :Ps2 and TIPORECEBIMENTO= :Ppag group by data order by data';

// mesas

with fdm.query1 do

begin

Close;

commandtext := cmd;

ParamByName('Ppag').AsString := tpag;

ParamByName('Pst1').AsString := st1;

ParamByName('Pst2').AsString := st2;

Open;

end;

// balcão

with fdm.query2 do

begin

Close;

commandtext := CmdBalcao;

ParamByName('Ppag').AsString := tpag;

ParamByName('Ps1').AsString := st1;

ParamByName('Ps2').AsString := st2;

Open;

end;

end;

2: begin

tpag := 'C';

cmd:='select (data) as ENTRADA, sum(total)as VALOR from DLV$tbvenda'+

' where data between :Pst1 and :Pst2 and Tipo= :Ppag group by data order by data';

cmdBalcao:= 'select (data) as ENTRADA, sum(total)as VALOR from DLV$tbpedido1'+

' where data between :Ps1 and :Ps2 and tiporecebimento= :Ppag group by data order by data';

// mesas

with fdm.query1 do

begin

Close;

commandtext := cmd;

ParamByName('Ppag').AsString := tpag;

ParamByName('Pst1').AsString := st1;

ParamByName('Pst2').AsString := st2;

Open;

end;

// balcão

with fdm.query2 do

begin

Close;

commandtext := CmdBalcao;

ParamByName('Ppag').AsString := tpag;

ParamByName('Ps1').AsString := st1;

ParamByName('Ps2').AsString := st2;

Open;

end;

end;

3: begin

tpag := 'T';

cmd:='select (data) as ENTRADA, sum(total)as VALOR from DLV$tbvenda'+

' where data between :Pst1 and :Pst2 and Tipo= :Ppag group by data order by data';

cmdBalcao:= 'select (data) as ENTRADA, sum(total)as VALOR from DLV$tbpedido1'+

' where data between :Ps1 and :Ps2 and tiporecebimento= :Ppag group by data order by data';

// mesas

with fdm.query1 do

begin

Close;

commandtext := cmd;

ParamByName('Ppag').AsString := tpag;

ParamByName('Pst1').AsString := st1;

ParamByName('Pst2').AsString := st2;

Open;

end;

// balcão

with fdm.query2 do

begin

Close;

commandtext := CmdBalcao;

ParamByName('Ppag').AsString := tpag;

ParamByName('Ps1').AsString := st1;

ParamByName('Ps2').AsString := st2;

Open;

end;

end;

end;

{ // mesas

with fdm.query1 do

begin

Close;

commandtext := cmd;

ParamByName('Ppag').AsString := tpag;

ParamByName('Pst1').AsString := st1;

ParamByName('Pst2').AsString := st2;

Open;

end; }

fdm.tbquery1.open;

fdm.tbquery1.refresh;

(fdm.tbquery1.FieldByName('valor') as tfloatfield).displayformat := '###,##0.00';

{ // balcão

with fdm.query2 do

begin

Close;

commandtext := CmdBalcao;

ParamByName('Ppag').AsString := tpag;

ParamByName('Ps1').AsString := st1;

ParamByName('Ps2').AsString := st2;

Open;

end; }

fdm.tbquery2.open;

fdm.tbquery2.refresh;

(fdm.tbquery2.FieldByName('valor') as tfloatfield).displayformat := '###,##0.00';

// entrega

with fdm.query4 do

begin

Close;

commandtext :=

'select Data , sum(total)as VALOR from DLV$tbentrega'+

' where entregue = :Pss and data between :Pst1 and :Pst2 group by data order by data';

ParamByName('Pss').AsString := 'S';

ParamByName('Pst1').AsString := st1;

ParamByName('Pst2').AsString := st2;

Open;

end;

fdm.tbquery4.open;

fdm.tbquery4.refresh;

(fdm.tbquery4.FieldByName('valor') as tfloatfield).displayformat := '###,##0.00';

// contas a pagar

with fdm.query3 do

begin

Close;

commandtext :=

'select (datapagamento)as PAGO, sum(vrdesconto)as VALOR from DLV$tbpagar1'+

' where datapagamento between :Pst1 and :Pst2 group by datapagamento order by datapagamento';

ParamByName('Pst1').AsString := st1;

ParamByName('Pst2').AsString := st2;

Open;

end;

fdm.tbquery3.open;

fdm.tbquery3.refresh;

(fdm.tbquery3.FieldByName('valor') as tfloatfield).displayformat := '###,##0.00';

//soma mesa

fdm.query1.open;

fdm.query1.First;

while not fdm.query1.Eof do begin

if fdm.query1['valor']<> null then

tot1:=tot1 + fdm.query1.fieldbyname('valor').AsFloat;

fdm.query1.Next;

end;

lmesa.Caption := 'TOTAL DAS MESAS R$ = ' + formatfloat('###,##0.00', tot1);

fdm.query1.close;

//soma balcão

fdm.query2.open;

fdm.query2.First;

while not fdm.query2.Eof do begin

if fdm.query2['valor']<> null then

tot2:=tot2 + fdm.query2.fieldbyname('valor').AsFloat;

fdm.query2.Next;

end;

lbalcao.Caption := 'TOTAL BALCÃO R$ = ' + formatfloat('###,##0.00', tot2);

fdm.query2.close;

//soma entrega

fdm.query4.open;

fdm.query4.First;

while not fdm.query4.Eof do begin

if fdm.query4['valor']<> null then

tot4:=tot4 + fdm.query4.fieldbyname('valor').AsFloat;

fdm.query4.Next;

end;

lentrega.Caption := 'TOTAL ENTREGA R$ = ' + formatfloat('###,##0.00', tot4);

fdm.query4.close;

//soma contas a pagar

fdm.query3.open;

fdm.query3.First;

while not fdm.query3.Eof do begin

if fdm.query3['valor']<> null then

tot3:=tot3 + fdm.query3.fieldbyname('valor').AsFloat;

fdm.query3.Next;

end;

lsaida.Caption := 'PAGO (SAÍDA) R$ = ' + formatfloat('###,##0.00', tot3);

fdm.query1.close;

// soma de entradas e saidas

lsaldo.Caption := 'TOTAL ENTRADAS R$ ' + formatfloat('###,##0.00', (tot1 + tot2 + TOT4)) +

' - TOTAL SAÍDAS R$ ' + formatfloat('###,##0.00', tot3) +

' = SALDO R$ ' + formatfloat('###,##0.00', (tot1 + tot2 + TOT4 - tot3));

end;

end;

procedure Tffluxo.FormClose(Sender: TObject; var Action: TCloseAction);

begin

fdm.tbquery1.Close;

fdm.tbquery2.Close;

fdm.tbquery3.close;

end;

procedure Tffluxo.RgTipoClick(Sender: TObject);

begin

somarExecute(Self);

end;

procedure Tffluxo.Action1Execute(Sender: TObject);

begin

Close;

end;

end.

-----------------------------------------------------------------------------------------------------------

Por favor,

poderia me ajudar?

Obrigado

[Jhonas]

Dynamic SQL error

SQL error code=303

conversion error from string "19/04/2009"

incompatible column/host variable data type

Error code: 249

Erro de conversão para string "19/04/2009"

coluna incompatível / variavel tipo data

'select (datapagamento)as PAGO, sum(vrdesconto)as VALOR from DLV$tbpagar1'+

' where datapagamento between :Pst1 and :Pst2 group by datapagamento order by datapagamento';

ParamByName('Pst1').AsString := st1;

ParamByName('Pst2').AsString := st2;

between :Pst1 and :Pst2

este comando pede como parametro um campo data e não string

então deveria ser:

'select (datapagamento)as PAGO, sum(vrdesconto)as VALOR from DLV$tbpagar1'+

' where datapagamento between :Pst1 and :Pst2 group by datapagamento order by datapagamento';

ParamByName('Pst1').AsDate := strtodate(st1);

ParamByName('Pst2').AsDate := strtodate(st2);

OBS: Favor não duplicar tópicos

abraço

[Antonio Maucyr Pasqualli F]

Jhonal foi mal, não me "toquei" na duplicação,

Achei o erro, era um erro muito "bobo", ele esta na data, o meu windows esta dd/MM/aaaa, acho que é o firebird ou o "script" esta mm/DD/aaaa, descobri isso as 5:30 da manha quando resolvi testar e deu certo, bom agora outra duvida, como faço para que fique dd/MM/aaaa?

Obrigado pela força jhonas!

Editado Abril 23, 2009 por Antonio Maucyr Pasqualli F

[Jhonas]

Achei o erro, era um erro muito "bobo", ele esta na data, o meu windows esta dd/MM/aaaa, acho que é o firebird ou o "script" esta mm/DD/aaaa, descobri isso as 5:30 da manha quando resolvi testar e deu certo, bom agora outra duvida, como faço para que fique dd/MM/aaaa?

O firebird trabalha com padrão americano para datas(mes/dia/ano), para usar o nosso padrão substitua as barras por pontos, na hora de inserir no banco e na hora de pesquisar dê um stringreplace(datetostr(data), '/', '.', [rfreplaceall]) na variavel da data

você pode utilizar tambem a formatação da seguinte maneira:

SimpleDateFormat sdfInput = new SimpleDateFormat( "dd/MM/yyyy" );

SimpleDateFormat sdfOutput = new SimpleDateFormat( "MM/dd/yyyy" );

where data = cast('2000/01/31' as date)

ou seja

where data = cast('AAAA/MM/DD' as date)

Testando na IBConsole

(nome da tabela usada alterado)

> --------------------------------

select * from tabelax

where data = '01/31/2000'

funciona

> ------------------------------

select * from tabelax

where data = '31/01/2000'

dá erro Conversion error from string "31/01/2000"

> ------------------------------

select * from tabelax

where data = '31.01.2000'

funciona

> ------------------------------

select * from tabelax

where data = '01.31.2000'

dá erro Conversion error from string "01.31.2000"

> ------------------------------

abraço