Exposing a start and end date in a single field.

Two parameters, one for start and one for end date. Then make sure they're in the same date/time format as your database and add them to the WHERE clause on your reader.

Make sure you set defaults properly so that if the user does not enter one or either of the dates it will still grab the correct set of records and you may also want to consider adding some testing to account for the user potentially setting an end date that's before the start date.

If you set the parameter type to date it'll show a nice date picker widget in the FME Server and you can then use those two parameters in the SQL query

Yes, so where you have those dates in that statement you need to replace them with the (properly formatted) parameters.

So e.g. you have a user parameter set up for the Start Date:

You can use that parameter in the Where clause:

Sorry, I just saw the second half of your comment show up. Thank you very much!

Most database readers have a WHERE clause option. Alternatively you could use a SQLCreator, which basically allows you to fire a SQL query at the database and work with the results.

I think I knocked some cobwebs loose and figured out what you were talking about. So I created two user parameters (StartDate, EndDate). I made them both Datetime fields with an example being (2021-02-05 00:00:00.000) format.

I keep getting a malformed statement error on this short WHERE statement:

sensor_data_time >= $(StartDate) AND sensor_data_time <= $(EndDate) AND measurement_id IN (100);

It works in SQL Server with the variable being the only difference:

WHERE sensor_data_time >= '2021-02-05 00:00:00.000' AND sensor_data_time <= '2021-02-010 23:59:00.000'

 AND measurement_id IN (100);

The error says:  Arithmetic overflow error converting expression to data type datetime.

That would imply that the datetime is the incorrect data type. I checked in fme and the data type set is a datetime field.

Ok, here is the solution;

I created the User Parameters as recommended. I then created a TestFilter in the main FME process. In the TestFilter I exposed the StartDate & EndDate User Parameters I had configured in the Navigator. The test clause was set with sensor_data_time >= $(StartDate) AND sensor_data_time <= $(EndDate). The output from the TestFilter (@Value(sensor_data_time) >= __StartDate_) is then connected to the rest of the process. Once posted to FME Server the Start and End Date parameters are available and included in the process.