Passing dates as parameters

Stimulsoft Reports.PHP discussion
rahulmes
Posts: 16
Joined: Fri Apr 08, 2011 2:12 am
Location: India

Passing dates as parameters

Post by rahulmes »

Hi.

I am trying to pass dates as parameters to my mySQL report. After going through the forums, I have taken these steps:

1. In the SQL query, we have to prefix an @ in the parameter. I have done this and this is my query:

Select * from visit_master, company_master where visit_master.CompanyID = company_master.CompanyID and DateOfVisit between @DateBegin and @DateEnd

2. In the DataSource dialog, I have defined 2 parameters DateBegin and DateEnd and defined their datatype as DateTime.

3. I have then declared 2 variables FromDate and ToDate. I have given their default values as 1/1/1970 0:00:00 AM.

4. I have assigned the expression values of the DateBegin and DateEnd parameters as FromDate and ToDate respectively.

5. I am passing the parameter values in the format of YYYYDDMM.

This is my hyperlink:
http://localhost/quickstart/stimulsoft/ ... d=20110601

When I run this query in my database, I get back 3 rows

Select * from visit_master, company_master where visit_master.CompanyID = company_master.CompanyID and DateOfVisit between 20110501 and 20110601

But, in the report no rows are getting returned. What could be the issue?

Thanks in advance for any help.
Jan
Posts: 1265
Joined: Thu Feb 19, 2009 8:19 am

Passing dates as parameters

Post by Jan »

Hello,

It very hard to say where your problem is located. Please show code which you use to run report and also we need see your report. You can check "SQL.Parameters from Dialog Form" sample report in Demo.exe application from standard delivery.

Thank you.
rahulmes
Posts: 16
Joined: Fri Apr 08, 2011 2:12 am
Location: India

Passing dates as parameters

Post by rahulmes »

Dear Jan

Thanks for the prompt reply. I am not using any code. The report is directly generated through the hyperlink.

I have attached the mrt file and a small sql file. Can you please instruct me on how to show the records between 2 visit dates? In other words, I want to see a list of visits made between 01-04-2011 and 01-05-2011.

Thanks.
Attachments
1015.mySQLRpt2.mrt
(11.8 KiB) Downloaded 689 times

[The extension sql has been deactivated and can no longer be displayed.]

Vladimir
Posts: 1462
Joined: Fri Apr 13, 2007 4:05 am
Location: Earth

Passing dates as parameters

Post by Vladimir »

Hello,

We have analyzed your reports and data. For Reports.Fx for PHP, there is another way to use parameters in SQL query. Please use the following query form:
Select * from visit_master, company_master where visit_master.CompanyID = company_master.CompanyID and DateOfVisit between {DateBegin} and {DateEnd}
In the GET request date can be specified in the following format (yyyy/MM/dd or yyyy-MM-dd):
Thank you.
rahulmes
Posts: 16
Joined: Fri Apr 08, 2011 2:12 am
Location: India

Passing dates as parameters

Post by rahulmes »

Thanks it worked, Vladimir.

However, I had to enclose the dates between single quotes like this if I used / or - as the separator:
Only this format worked well without quotes,
Vladimir
Posts: 1462
Joined: Fri Apr 13, 2007 4:05 am
Location: Earth

Passing dates as parameters

Post by Vladimir »

Hello,

You can also use the yyyy-MM-dd date format, it should correctly read in the last prerelease builds of Reports.Fx for PHP.

Thank you.
usman
Posts: 2
Joined: Mon Feb 10, 2020 5:46 am

Re: Passing dates as parameters

Post by usman »

I am facing with the same problem code is like below if anyone can help me
Select id,name,publishing_date from books where (id = {bid} or category_id = {gid} or publishing_date between '{DateBegin}' and '{DateEnd}')
it gives me this error
The error of compilation
The error of compilation is found in the 'Report' report:
The name 'DateBegin' does not exist in the current context
Lech Kulikowski
Posts: 6163
Joined: Tue Mar 20, 2018 5:34 am

Re: Passing dates as parameters

Post by Lech Kulikowski »

Hello,

Please send us a sample report with test data that reproduces the issue for analysis.

Thank you.
shravan.pitla
Posts: 27
Joined: Wed Jul 22, 2020 1:11 pm

Re: Passing dates as parameters

Post by shravan.pitla »

Is this issue resolved i am also facing the same problem with date parameters
Attachments
Test login statistic Report.mrt
(30.62 KiB) Downloaded 262 times
Lech Kulikowski
Posts: 6163
Joined: Tue Mar 20, 2018 5:34 am

Re: Passing dates as parameters

Post by Lech Kulikowski »

Hello,

Please send us a test data fro your report.

Thank you.
Post Reply