Output Data : date is now datetime, making my output crashing

[simonaubert_bd]

Hello all,

I try to ouput a flow in a MonetDB SQL database. As you can see, I only take very simple field types

However I get this error message :

Error: Output Data (3): Error creating table "exemplecomparetable.toto": [MonetDB][ODBC Driver 11.44.0][MONETDB_SAU]Type (datetime) unknown in: "create  table "exemplecomparetable"."toto" ("ID" int,"Libellé" char(50),"Date d"
syntax error in: ""Prix""
CREATE  TABLE "exemplecomparetable"."toto" ("ID" int,"Libellé" char(50),"Date de Maj" datetime,"Prix" float,"PMP" float)

I really don't understand why the hell would Alteryx  change my date into datetime. This doesn't make sense at all. Any idea ?

Best regards,

Simon

[Samantha_Jayne]

Morning Simon,

I have not seen this before myself, but I know databases can be a bit funny about these things. I have worked with databases which dont like datetime and prefer date and time to be separated.

Can I check and I am sure your answer will be Yes: Was your select tool there when running this workflow?

TIA

[simonaubert_bd]

Hello @Samantha_Jayne 

First of all, thanks for your time and your answer.

Yes, my Select Tool was here when the workflow crashed. At first, I had v_wstring, Alteryx put it in nvarchar that wasn't supported by the database so I changed it to String.

I'm not sure the database is at fault here : the query is built by Alteryx and appears with that non-sense "datetime" in logs while 1/I don't want a datetime but a date. 2/datetim is not supported in MonetDB and to be frank, this is not a widely supported field type (is it in the SQL norm? I have serious doubts here).

Best regards,

Simon

[Samantha_Jayne]

Morning @simonaubert_bd 

Some databases can handle it but personally I prefer to keep it separate, raised in MSSQL 😁

If all the right data types are set in your select and you are still having issues pushing your data up to the database, we may need to investigate if this is a bug.

However when checking to see if MonetDB is on the list of data sources I couldn't see it, does it have another name? Can you check this list, I would hate for you to raise a support call for them to say, sorry not supported at this time. https://help.alteryx.com/20221/designer/data-sources

Always happy to help,

[simonaubert_bd]

Hello @Samantha_Jayne  No, it's not on the supported list, that's why I haven't create a ticket ;)

However, even if not officially supported, it still be an ODBC/SQL database which follows the norm (at least the basics). And there is no reason, even in this case, that a date is migrated to a datetime. Interoperability is key to provide high value to customer.

Best regards,

Simon

[Samantha_Jayne]

No problem @simonaubert_bd.

You may have already done this, but if you haven't it may be worth adding an idea to the ideas page to get your database supported. Ideas is where product looks first and you can link this discussion to it, if you have a larger user base internally get them to support your idea so we can know how many people this affects.

[simonaubert_bd]

Hello @Samantha_Jayne 

Done here : https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Generic-in-memory-odbc-connection-please-sto...

I found the same issue with informix too.