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.

The thing that threw me off on adding them as new user parameters is that it doesn't pull the date/time from the actual field. I have the WHERE clause already built to push out two weeks. Just have that end-user change the select statement? I was trying to figure out a way to get them to grab two dates from a calendar or punch in MM/DD/YYYY and it would extract the data. Currently, I have it grabbing everything two weeks back from today.

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

To clarify, I am able to write a statement like:

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 (XXX,XXXX);

This SQL works. I was trying to see if there was a way to make it where people not familiar with SQL could punch in dates and move on. I was playing around trying to expose a tester or two, but I didn't have much luck.

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:

How does it pull from the sensor_data_time field automatically?

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

Is the WHERE clause buried in the newly published user parameter? I'm not seeing those same parameters:

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.

What transformer was your screenshot coming from? I think that is exactly what I am looking to use. The reason I was confused is my user parameter creation dialog doesn't look like your screenshot. When I am configuring the user parameters there is no place to include a WHERE statement.

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.

I was able to expose the WHERE statement with no problem in FME server, but it bombs when I try to include the StartDate and EndDate parameters instead of the exposed WHERE statement on Desktop. The error is that Arithmetic overflow which makes me think the DATETIME of the sensor data needs to be formatted. If anyone has a trick on how to make this play nice I would really appreciate it.

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.