Query issue - it does not work

[Former Member]

Hi there,

i wrote this query:

SELECT T1.[CardCode], T1.[CardName], SUM(T0.[DocTotal]),

(Select Top 1 T2.Descr
 From UFD1 T2 
Where T2.FldValue = T1.U_gruppe AND TableID = 'ACRD' AND FieldID = '1') AS ' Kundengruppe'

FROM OINV T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
WHERE T0.[DocDate] >= [%1] AND  T0.[DocDate] <= [%2]
GROUP BY T1.[CardCode], T1.[CardName], T1.U_gruppe

If I execute it, i get the following error message :

1). [Microsoft][SQL Native Client][SQL Server]Arithmetic overflow error converting expression to data type datetime. 'Benutzerdefinierte Werte' (CSHS)

What does it mean ? Where is my error ?

Regards Steffen

[former_member210784 · Accepted solution]

Hello.

You have to put a ' character to date parameter.

SELECT T1.[CardCode], T1.[CardName], SUM(T0.[DocTotal]), 
 (Select Top 1 T2.Descr
  From UFD1 T2 
  Where T2.FldValue = T1.U_gruppe AND TableID = 'ACRD' AND FieldID = '1') AS ' Kundengruppe'
 
FROM OINV T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
WHERE T0.[DocDate] BETWEEN '[%1]' AND '[%2]'
GROUP BY T1.[CardCode], T1.[CardName], T1.U_gruppe

Try run the query an let us know the result.

[former_member204969]

Try this one:

declare @d1 datetime
declare @d2 datetime
/*Select T0.Docdate from OINV T0 where T0.Docdate between [%0] and [%1]*/
set @d1=[%0]
set @d2=[%1]

SELECT T1.[CardCode], T1.[CardName], SUM(T0.[DocTotal]),
 
(Select Top 1 T2.Descr
 From UFD1 T2 
Where T2.FldValue = T1.U_gruppe AND TableID = 'ACRD' AND FieldID = '1') AS ' Kundengruppe'
 
FROM OINV T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode 
WHERE T0.[DocDate] >= @d1 AND  T0.[DocDate] <= @d2
GROUP BY T1.[CardCode], T1.[CardName], T1.U_gruppe