Hi. I am using iWay Data Quality Center (DQC/DQM), and I keep running into an issue regarding date conversions.

I am using a JDBC reader within a flow in order to pull the system date from SQL Server. I need this system date to show up in yyyy-mm-dd format without the timestamp. Query for the JDBC reader is provided below.

SELECT
convert(date,getdate()) AS DT,
ISN AS PARCEL_ISN,
NODE_ISN,
USER_ISN,
DOC_TYPE AS TYPE
FROM StagingDexFLOW.dbo.MCG_UWP_PARCEL


When I run this query directly against the SQL Server, it returns with no issues, but when I run via DQC, I receive the following error:

Runtime error in jdbc reader.
java.sql.SQLException: The type of column DT (nvarchar) does not seem to be convertible to date
at com.ataccama.dqc.tasks.io.jdbc.bindings.SqlBindingBase.checkDateTypevalidity(SqlBindingBase.java:60)
at com.ataccama.dqc.tasks.io.jdbc.bindings.SqlBindingBase.createDayBinding(SqlBindingBase.java:76)
at com.ataccama.dqc.tasks.io.jdbc.read.JdbcReaderInstance.createSqlBinding(JdbcReaderInstance.java:179)
at com.ataccama.dqc.tasks.io.jdbc.read.JdbcReaderInstance.createSqlBindings(JdbcReaderInstance.java:163)
at com.ataccama.dqc.tasks.io.jdbc.read.JdbcReaderInstance.run(JdbcReaderInstance.java:131)
at com.ataccama.dqc.processor.internal.runner.ComplexStepNode.runNode(ComplexStepNode.java:57)
at com.ataccama.dqc.processor.internal.runner.Node.run(Node.java:123)
at java.lang.Thread.run(Unknown Source)


In the reader's columns tab, I have the DT field set up as data type DAY. I have researched Google and found numerous ways to do date conversions within SQL, but no matter how many ways I try to do the conversion from timestamp to date, I end up with the same error message.

I have also tried editing the flow by changing the data type to string and casting the getdate field as a varchar, but that of course, produced output in a different format than desired.

Since the query executes ok within SQL Server, I believe it is something regarding the particular step within DQC.

What is the best way to avoid this error message and have the flow use the date portion only of the system timestamp.

Thank you,
Andrea Davis

After some further analysis, I have found the following syntax works:

DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

You should keep the datatype as DAY.