Querying Data According to the Start Time and End Time

Version

Expected Effect

If a column of data in a database is of the date type, how can you query the data within a certain period?

Implementation Method

You can define two report parameters (one for the start time and the other for the end time). Then, you can set the filter condition that the dates in the date column are greater than/equal to the start time and less than the end time.

Template Preparation

1. Create a general report and create a dataset ds1 with the SQL statement SELECT * FROM Sales_Volume.

2. Design the table, as shown in the following figure.

3. Choose Template > Template Parameter. Add the parameters starttime and endtime. The following figure shows the effect.

Widget Adding

Enter the parameter panel, click Add All in the upper right corner, and select Date Widget for the two parameters, respectively, as shown in the following figure.

Filter Condition Setting

Double-click cell A2, select Filter, and add the filter condition for cell A2, as shown in the following figure. The filter condition means that the order dates are greater than/equal to the start time and less than the end time.

Note:

When the data on date or time in a database is of the text type, you are advised to convert the data to the date or time type. It is because the comparison principle of data of the text type is not suitable for the comparison of data of the date type.

Effect Display

PC

Save the template and click Pagination Preview. Select a start date and an end date, and click Query. The following figure shows the effect.

Mobile Terminal

The following figure shows the preview effect on the DataAnalyst app and the HTML5 terminal.

Completed Template

For details, you can download the template Start Time and End Time 1.cpt.

Template Preparation

1. Create a report, and create a data query ds1 with the SQL statement: SELECT * FROM Orders where 1=1 ${if(len(starttime)=0,""," and `Order Date` >='"+starttime+"'")}${if(len(endtime)=0,""," and `Order Date` <'"+endtime+"'")}.

The following explains the clauses in the statement.

• where 1=1 means that the condition is always true, preventing errors caused by the absence of valid content after the WHERE clause when no parameter condition exists.

• len(starttime)==0 means that the value of the parameter starttime is empty.

• In and `Order Date` >='"+starttime+"'", starttime is a parameter, while the + operator is used for string concatenation.

• ${if(len(endtime)=0,""," and `Order Date` <'"+endtime+"'")} means that the parameter starttime is defined. If the parameter value is empty, the query returns all data. If the parameter value is not empty, the query returns the data with the order dates greater than or equal to the specified date. This definition is primarily made to ensure that all data is queried when the parameter value is empty.

• The same principle applies to the parameter endtime. No further explanation is provided here.

2. Design the table, as shown in the following figure.

Widget Adding

Enter the parameter panel, click Add All in the right corner, and select Date Widget for the two parameters, respectively, as shown in the following figure.

Effect Display

The effect is the same as that shown in section "PC."

Completed Template

For details, you can download the template Start Time and End Time 2.cpt.

1. Generally, you need to validate the data. You need to ensure that the start date and end date are not empty, that the end date must be later than the start date, and that the end date must be within a certain period after the start date. Otherwise, relevant prompts will appear.

2. In some cases, you may want to realize the effect that you can query data by year, month, or day, respectively.