Date Range in Query

[Former Member]

Hi All,

We have created a query report which we have created as a stored procedure and we are executing the sp in SBO to get the result. However, there is one issue, we have passed the variables in the sp as well as the fms to get the date range, but if we input the date manually, the result shown is incorrect!!

It works fine when we tab on the from date, scroll down and select the date from the cfl!! This is not acceptable to the client. They want to enter the dates manually and not select from the cfl.

Please advise what is wrong here and how to correct this issue?? Is there any limitation of this sort..

Thanks,

Joseph

[Former Member]

Done

[former_member218051]

Hi Joseph,

I had a similar problem.

I changed my code in SP as follows and it is working for me .

Declare @LastDay as Datetime

set @lastday = GETDATE()

set @lastday = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@lastday))),DATEADD(mm,1,@lastday)),101))

thanking you

Malhaar

[Former Member]

Hi Malhaar,

Thank and I will test this and update.

Regards,

Joseph

[jitin_chawla]

Hi,

Have you confirmed that the format for the date manually entered is correct?

You can post the code probably and experts can have a look and check the issue and provide a solution/try for a solution.

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi Jitin,

I too have tried by entering the date format manually, but still does not work. In the SP, the format entered is 104 and i guess its the correct format. In sbo, we are trying to enter the date as displayed in the cfl but still no help.. The report works fine only if we select the date from the cfl and not if we input the date manually.

Is there anyway, i can attach word doc to this thread.. the query is over 64000 characters not sure how many pages will be consumed.. so i did not paste it here.

Thanks,

Joseph

[kvbalakumar]

Hi Joseph,

It won't need to paste the full query here.

Just paste the part of the query in which the Date parameter is captured and also in which part the parameter is linked with the query is enough for initial investigation.

Regards,

Bala

[Former Member]

Hi Bala,

The fms used in SBO is mentioned below;

Declare @datefrom VARCHAR(20)
DECLARE @dateto VARCHAR(20)

Select @datefrom = T0.DocDate FROM OINV T0 Where T0.DocDate = '[%0]'
Select @dateto = T0.DocDate FROM OINV T0 Where T0.DocDate = '[%1]'
EXEC TEST @datefrom ,@dateto

Thanks,

Joseph

[kvbalakumar]

Hi Joseph,

Instead of getting the date as string, try by changing the below declaration part

Declare @datefrom VARCHAR(20)
DECLARE @dateto VARCHAR(20)

by this

Declare @datefrom datetime
DECLARE @dateto datetime

I think, then you need to chenge the same in the SP too

Regards,

Bala

[Former Member]

Hi,

Try to change the variable declaration.

DECLARE @datefrom as datetime

DECLARE @dateto as datetime

Thanks.

Clint