Regarding Query

[Former Member]

Hi Experts,

I had made 1 Query in which Posting Date & Due is used in selection Criteria.........But it is not working it shows me an error of DateTime.

This was possible in SAP 2005 B .I have upgraded SAP to 2007 B PL 04.In 2007B it shows error.The working of company is effected bcoz of these........Is the methodology changed.

In selection criteria i have used the following Criteria(T0.DueDate >= '[%1]' and T0.DueDate <= '[%2]')

Regards-

Mona.

[former_member583013 · Accepted solution]

Mona,

If you paste your complete query it will be easy to understand and reply

Suda

[Former Member · Accepted solution]

Hi suda,

SELECT T0.DocNum, T0.Status, T0.U_Priority,

T0.DueDate, T0.PostDate, T0.ItemCode,

T0.PlannedQty, T0.CmpltQty, T0.RjctQty,

( T0.PlannedQty-T0.CmpltQty- T0.RjctQty) as

'Open Qty',T0.U_TimeReq,

(((T0.PlannedQty-T0.CmpltQty- T0.RjctQty)*T0.U_TimeReq)/60)

as 'Total Time Reqd',T0.U_TimeReq, T0.U_SHOP,

T0.U_FCName FROM OWOR T0 inner join wor1 t1 ON T0.DocEntry = T1.DocEntry WHERE (T0.Series = '31' or

T0.Series = '130') AND T0.Status = '[%0]' and

(T0.DueDate >= '[%1]' and T0.DueDate <= '[%2]')

or (T0.PostDate >= '[%3]' and T0.PostDate <= '[%4]')

and T1.ItemCode ='[%7]'.

Regards-

mona

[Former Member · Accepted solution]

Hi Mona,

Please use the following query to resolve the issue :

SELECT T0.DocNum, T0.Status, T0.U_Priority,

T0.DueDate, T0.PostDate, T0.ItemCode,

T0.PlannedQty, T0.CmpltQty, T0.RjctQty,

( T0.PlannedQty-T0.CmpltQty- T0.RjctQty) as

'Open Qty',T0.U_TimeReq,

(((T0.PlannedQty-T0.CmpltQty- T0.RjctQty)*T0.U_TimeReq)/60)

as 'Total Time Reqd',T0.U_TimeReq, T0.U_SHOP,

T0.U_FCName FROM OWOR T0 inner join wor1 t1 ON T0.DocEntry = T1.DocEntry WHERE (T0.Series = '31' or

T0.Series = '130') AND T0.Status = [%0] and

(T0.DueDate >= [%1] and T0.DueDate <= [%2])

or (T0.PostDate >= [%3] and T0.PostDate <= [%4])

and T1.ItemCode =[%7]

Regards,

Rakesh Pati

SAP Business One Forum Team

[former_member187989]

Append this in query

(CONVERT(DATETIME,T0.DueDate,103) >= '01/SEP/2008' and CONVERT(DATETIME,T0.DueDate,103)<= '30/SEP/2008') 
or (CONVERT(DATETIME,T0.PostDate,103) >= '01/APR/2008' and CONVERT(DATETIME,T0.PostDate,103) <= '30/APR/2008')

Jeyakanthan

[former_member187989]

SELECT T0.DocNum, T0.Status, T0.U_Priority, 
T0.DueDate, T0.PostDate, T0.ItemCode, 
T0.PlannedQty, T0.CmpltQty, T0.RjctQty, 
( T0.PlannedQty-T0.CmpltQty- T0.RjctQty) as
'Open Qty',T0.U_TimeReq, 
(((T0.PlannedQty-T0.CmpltQty- T0.RjctQty)*T0.U_TimeReq)/60)
as 'Total Time Reqd',T0.U_TimeReq, T0.U_SHOP, 
T0.U_FCName FROM OWOR T0 inner join wor1 t1 ON T0.DocEntry = T1.DocEntry WHERE (T0.Series = '31' or 
T0.Series = '130') AND T0.Status = '[%0]' and 
(CONVERT(DATETIME,T0.DueDate,103) >= '01/09/2008' and CONVERT(DATETIME,T0.DueDate,103) <= '30/09/2008') 
or (CONVERT(DATETIME,T0.PostDate,103) >= '01/08/2008' and CONVERT(DATETIME,T0.PostDate,103) <= '31/08/2008') 
and T1.ItemCode ='[%1]'

Jeyakanthan

[Former Member]

hi,

give the date as yyyymmdd then it will work.....

normally we use to give date by dd/mm/yy or mm/dd/yy....

but the system will read yyyymmdd

try this

regards

sandip

[Former Member]

Hi Mona,

Can you send the complete query ?

Samir Gandhi

[former_member187989]

Mona,

Can you post error free query ?

Jeyakanthan