Hi I've right clicked on a table in the sqlcreator and chosen 'Script as select' and done a very trivial where clause. the problem is the columns are all varchar200 and not dates .. what have I missed?

Hi @ldonofrio​ ,

could you please provide sample workspace and data to understand better your issue?

Thanks,

The problem shows up when I try to use dates in the where clause of a sqlcreator to limit results (commented out below) ,

I added an inspector and I saw that (when the mouse hovers over the BSEG_ST_DT column) that it is of type " fme_varchar(200)" however the sql DB has it as a date so I assume that there is a setting that I haven't chosen perhaps on the connection??

any way with the line uncommitted I get the error below:

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `SELECT top(100) * 

FROM GIS_B2B.[vw_VIC_MIRN_BILL_HISTORY_V]

where [BSEG_ST_DT] < 2019-12-31 00:00:00 and [BSEG_END_DT] > 2019-01-01 00:00:00'. Provider error `(-2147217900) Incorrect syntax near '00'.'

A fatal error has occurred. Check the logfile above for details

The below feature caused the translation to be terminated

SQLCreator_<Rejected> (TeeFactory): SQLCreator_<Rejected>: Termination Message: 'SQLCreator output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''

SQLCreator_<Rejected>: Termination Message: 'SQLCreator output a <Rejected> feature. To continue translation when features are rejected, change 'Workspace Parameters' > Translation > 'Rejected Feature Handling' to 'Continue Translation''

A fatal error has occurred. Check the logfile above for details

Kind Regards

in case you can't read the SQL in the query I've copied it here.

Ta

FME_SQL_DELIMITER ;

SELECT top(100) * 

FROM GIS_B2B.[vw_VIC_MIRN_BILL_HISTORY_V]

--where [BSEG_ST_DT] < $(parDec) and [BSEG_END_DT] > $(parJan);

Hi @ldonofrio​ ,

I have no SQL Server DBMS to reproduce yor issue, so I try to give you some general tips based on your feedback:

• did you try the Microsoft SQL Reader instead of the SQLCreator?
• your workspace log says that SQL complains about date format, maybe the date format expected by SQL Server provider is different from the date format built in your query; try, for example, to enclose the sting in single quotes.

By the way, did you try to execute the query directly with an SQL Server Cient tool?

Hope that helps!

hi, I changed the 'FME_SQL_DELIMITER' and cast a sql variable and used single quotes for my fme parameter 'parJan' and it worked.

Thanks for you reply