Hi,
I'm using the SQL Executor transformer to pull back the most recent records from a SQL server DB since the ETL was last run. The last run datetime will be held in in a Postgres table and pushed through as a parameter to the SQL Executor, the issue here arises when I push the datetime parameter through to the SQL Executor
As a test and to trap the error I've used the attribute creator transformer to generate a datetime attribute which would mimic the last run datetime from Postgres, and hence push this through as a parameter. I've set the value = '2018-01-14 09:18:00'
If I run SQL Executor with a set datetime ('2018-01-14 09:18:00') the SQL executor runs successfully and returns records. However if I run the same query replacing the specified datetime value with the the parameter I get the following error message, despite the value and format being identical?
--'. Provider error `(-2147217900) Incorrect syntax near '09'.'
The error message points to an issue with the time element of the parameter?
The SQL statement below, for reference I've commented out the 'where clause' which works perfectly fine.
SELECT cjl.job_log_number, cj.job_number, cj.job_notes, cj.site_code, cj.plot_number, cj.job_entry_date, cjl.status_code, cjl.allocated_officer, cjl.logged_date, cj.contract_code, cj.contract_area_code, cj.job_status_flag,
job.work_type_code
FROM [confirm].job AS cj LEFT OUTER JOIN
(SELECT work_type_code, order_number, contract_code
FROM [confirm].order_header) AS job ON cj.order_number = job.order_number AND cj.contract_code = job.contract_code LEFT OUTER JOIN
[confirm].job_status_log AS cjl ON cj.job_number = cjl.job_number
WHERE cjl.logged_date > @Value(last_run) -- this doesn't work
-- WHERE cjl.logged_date > '2018-01-14 09:18:00' -- this works
I'm using FME version 2017.1 and any help greatly appreciated.
Many Thanks,
Chris
