cancel
Showing results for 
Search instead for 
Did you mean: 

Problem on Query Generator

Former Member
0 Kudos

Hi Expert,

I made the following query:

SELECT T0.[RefDate], T0.[TransId], T1.[Account], T1.[ShortName], T1.[Debit], T1.[Credit], T1.[ProfitCode] FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE T0.[RefDate] between [%0] and [%1]

However error log appears as follows:

1). [Microsoft][SQL Native Client][SQL Server]Arithmetic overflow error converting expression to data type datetime. 'User-Defined Values' (CSHS)

Can you please figure out how to solve this problem?

Thanks...

ArlmiPJ

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Do you try to use this query for formatted search? The error message links to FMS.

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Can you please elaborate what is the formatted search because I do not know the FMS all about.

Actually I tried to run the query in SQL Management Studio and it seems the refdate column includes time.. it shows like this '2011.01.31 00:00:00.000'

Thanks for your help..

Regards,

ArlmiPJ

former_member541807
Active Contributor
0 Kudos

hi ArlmiPJ,

SQL Management studio has different syntax from within SBO especially if we use variables like

'[%0]'

. please try to use the ff. Query below

Rund this Query in SQL Management Studio

SELECT     T0.RefDate, T0.TransId, T1.Account, T1.ShortName, T1.Debit, T1.Credit, T1.ProfitCode
FROM         OJDT AS T0 INNER JOIN
                      JDT1 AS T1 ON T0.TransId = T1.TransId
WHERE     (T0.RefDate BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-02-13 00:00:00', 102))

Run this Query from SBO

SELECT T0.RefDate, T0.TransId, T1.Account, T1.ShortName, T1.Debit, T1.Credit, T1.ProfitCode 
FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId 
WHERE T0.RefDate between '[%0]' and '[%1]'

regards,

Fidel

Former Member
0 Kudos

Thanks for your reply it is already working great.

Regards,

ArlmiPJ

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi.......

Try this......

SELECT T0.RefDate, T0.TransId, T1.Account, T1.ShortName, T1.Debit, T1.Credit, 
T1.ProfitCode FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId 
WHERE (T0.RefDate >='[%0]' and T0.RefDate <='[%1]')

Regards,

Rahul

Former Member
0 Kudos

Hi ArlmiPJ,

Try this..

SELECT T0.RefDate, T0.TransId, T1.Account, T1.ShortName, T1.Debit, T1.Credit, T1.ProfitCode FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId WHERE T0.RefDate >='[%0] and T0.RefDate  <='[%1]'

Thanks,

Srujal Patel

Former Member
0 Kudos

Hi,

Thanks for your help but still same error log appear.

Regards,

ArlmiPJ

Former Member
0 Kudos
SELECT T0.RefDate, T0.TransId, T1.Account, T1.ShortName, T1.Debit, T1.Credit, T1.ProfitCode FROM OJDT T0 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId 
WHERE T0.RefDate >='[%0]' and T0.RefDate  <='[%1]'
former_member204969
Active Contributor
0 Kudos

You might have entered the dates in a wrong format. First try to select from the existing values.

This code works for me perfectly:

SELECT T0.RefDate, T0.TransId,
 T1.Account, T1.ShortName, T1.Debit, T1.Credit, T1.ProfitCode 
FROM OJDT T0
 INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId 
WHERE T0.RefDate between [%0] and [%1]