Warning converting clover type: date to sql type: DATE

CloverDX Platform
1

hi,

i’m trying to clear a warning in DBOutputTable…

i’m passing in data from a ComplexDataReader, my metadata has a field “lot_date” of type date, to a postgres table with a matching column. but i’m receiving a warning:

Invalid SQL query. Incompatible types - field lot_date, clover type: date, sql type: DATE

it’s the same error message whether i map it via DB Table/Field mapping

$account:=account;$lot_number:=lot_number;$lot_date:=lot_date;

or i just use the sql query

INSERT INTO "tlh_test"."clover__import_table" ("account", "lot_number", "lot_date") VALUES ($account, $lot_number, $lot_date)

or

INSERT INTO "tlh_test"."clover__import_table" ("account", "lot_number", "lot_date") VALUES ($account, $lot_number, ($lot_date)::date)

it actually runs correctly in all of the above configurations, but continues to warn me. any thoughts on how i can make it happy?
thanks!

designer v. 4.4.0.011
postgres v. 9.5

2

Hi,

I came across exactly the same thing a while back, wondering what is wrong. The answer is actually in PostgreSQL documentation - PostgreSQL’s DATE is a plain date information without any notion about the time. CloverETL’s date is on the other hand essentially timestamp. The warning basically means, you’re losing precision when you try to insert into a database.

So how to fix it? Easily, either set your database field to TIMESTAMP, or…

Open your metadata and select the field which is causing problems. On the right hand side of the dialog (when you scroll down a tiny bit) is a “Format” option. There is a heuristic which determines what kind of information given field is suppose to carry.

  • If you leave it blank (i.e. set to default), it is basically exactly the same as you’d set format to DATE & TIME - engine will check against TIMESTAMP data type in your database

  • If you set DATE only format, your database is going to be checked against DATE data type

  • If you set TIME only format, your database is going to be checked against TIME data type

Hope this helps.

3

hi pavel,

thanks for the tip. the designer ui shows “yyyy-MM-dd” as the default setting in the metadata for the date’s format so i assumed that was what it was going to do. as you say, when i actually changed it to “yyyy-MM-dd” the warning went away.

cheers!