Erro em Consulta

[Former Member]

Escrevi uma consulta porem esta dando erro, alguem pode me ajudar?

SELECT

T0.[DocDate],

T0.[SubStr],

T0.[SeriesStr],

T0.[TaxDate],

T0.[CardCode],

T2.[State1],

T2.[State2],

ISNULL((SELECT TOP 1 T1.[CFOPCode] FROM PCH1 T1 WHERE T0.DocEntry = T1.DocEntry),0) AS 'CFOP',

ISNULL((SELECT SUM(PCH4.BaseSum) FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 15),0) AS 'BASE ICMS',

ISNULL((SELECT TOP 1 PCH4.TaxRate FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 15),0) AS 'ALIQUOTA ICMS',

ISNULL((SELECT SUM(PCH4.TaxSum) FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 15),0) AS 'VL. ICMS',

ISNULL((SELECT SUM(PCH4.BaseSum) FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 14),0) AS 'BASE IPI',

ISNULL((SELECT SUM(PCH4.TaxSum) FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 14),0) AS 'VL IPI'

FROM OPCH T0

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

WHERE

(T0.[DocDate] >= '[%0]' AND T0.[DocDate] <= '[%1]')

o erro é "Conversion failed when converting datetime from character string".

[Former Member]

Elaine, boa tarde... segue alteração para que a execução funcione no SAP.

Não verifiquei a coerência do código.

 declare @datai smalldatetime
declare @dataf smalldatetime

set @datai = (select top 1 docdate from opch T0 where T0.docdate >= [%0] order by docdate asc)

set @dataf = (select top 1 docdate from opch T0 where T0.docdate <=  [%1] order by docdate desc)

SELECT

T0.DocDate, 
T0.SubStr, 
T0.SeriesStr, 
T0.TaxDate, 
T0.CardCode, 
T2.State1, 
T2.State2, 
ISNULL((SELECT TOP 1 T1.CFOPCode FROM PCH1 T1 WHERE T0.DocEntry = T1.DocEntry),0) AS 'CFOP',
ISNULL((SELECT SUM(PCH4.BaseSum) FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 15),0) AS 'BASE ICMS',
ISNULL((SELECT TOP 1 PCH4.TaxRate FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 15),0) AS 'ALIQUOTA ICMS',
ISNULL((SELECT SUM(PCH4.TaxSum) FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 15),0) AS 'VL. ICMS',
ISNULL((SELECT SUM(PCH4.BaseSum) FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 14),0) AS 'BASE IPI',
ISNULL((SELECT SUM(PCH4.TaxSum) FROM PCH4 WHERE T0.DocEntry = PCH4.DocEntry AND PCH4.StaType = 14),0) AS 'VL IPI'

FROM OPCH T0 

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode 

WHERE 

(T0.DocDate >= @datai AND T0.DocDate <= @dataf) 

At. Junior.

[Former Member]

Funcionou,

Muito obrigada Junior.