Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    DQM date conversion in JDBC Reader
Go
New
Search
Notify
Tools
Reply
  
DQM date conversion in JDBC Reader
 Login/Join
 
Member
posted
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


iWay Data Quality Center Version: 7.0.1.ga-2010-12-23

OS: Windows 7 - 64 bit
 
Posts: 2 | Registered: February 15, 2013Reply With QuoteReport This Post
Member
posted Hide Post
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.


iWay Data Quality Center Version: 7.0.1.ga-2010-12-23

OS: Windows 7 - 64 bit
 
Posts: 2 | Registered: February 15, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    DQM date conversion in JDBC Reader

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.