Hi
I am getting the error below on a date column when importing a MS Access table:
ConvError: Input Data (2): Valid From: "1111-11-11 00:00:00" is not a valid DateTime
This date (1111-11-11) is used to classify certain records and is not used as a date. Alteryx is currently converting all cases to NULL values. There are blank date records which are also being assigned NULL so I am losing the ability to isolate the 1111-11-11 records within Alteryx.
Alteryx Input Tool doesn't seem to allow me to override the inferred data types so I am unable to import this date field as a string and then parse it into a date manually.
Is there a way round this?
Thanks for your time.
Solved! Go to Solution.
@jpg34 One option to change the data type to string for this date field in Access DB before importing, then you can change the string data type back to DateTimeformat in Alteryx
Thanks binuacs - I think that would work. However I don't want users altering data types within MS Access if I can help it as I can see it causing potential issues.
@jpg34 I think what @binuacs meant is to cast the date into a string during the query step so it comes as a string in Alteryx without having to modify the value or the type in the source database.
This approach allows you to separate the records with the "1111-11-11" date in your workflow. However, any attempt to convert it back into a Date/DateTime type will again result in a conversion error and a NULL value. Just a limitation of Alteryx, I suppose.
Thank Peachyco. I did try to recast within Alteryx but didn't find a way. It seemed that the 1111-11-11 had already been cast as null date values by the Input Tool so any recasting didn't have any effect. I am quite new to Alteryx so please correct me if I have missed something. Thanks
No, the casting has to happen in the query itself, before (or while) the data is loaded in Alteryx.
In this example, I'm casting the datetime [Ref Date] to the string [String Date] as I'm querying Table1 in MS Access:
select
ID,
Format([Ref Date], "yyyy-mm-dd") as [String Date],
Detail
from Table1
This is the data that comes to the Input Data tool:
Ok thanks, where do you put the code in blue? Is that in MS Access or Alteryx? I have a feeling this is SQL code within MS Access? At the moment I am just linking to an existing table from within MS Access by selecting it. Thanks
@jpg34 It's in Alteryx, in the Input Data tool.
I think you're using the "Tables" tab of the Input Data tool to select the entire table. If you want to use SQL code, you can use the "SQL Editor" tab instead.