Issue retaining the data of an invalid date from a table in MSAccess when importing

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.

Input

Date Time

Accepted answers

Peachyco

@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.

Alteryx - jpg34 Invalid date 2.png

binu_acs

@jpg34 The approach from @Peachyco should work in your use case. When you connect the input tool with the Access DB it will prompt the window in there select the SQL Editor and write the SQL to pull the data from the DB, and for the date field you can use the Format function as mentioned above

jpg34

You are right! Issue resolved. Thank you for sticking with me @Peachycoand for your initial response @binu_acs

All comments

binu_acs

@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

jpg34

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.

Peachyco

@jpg34 I think what @binu_acs 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.

jpg34

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

Peachyco

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:

Alteryx - jpg34 Invalid date.png

jpg34

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

Peachyco

@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.

Alteryx - jpg34 Invalid date 2.png

binu_acs

jpg34

You are right! Issue resolved. Thank you for sticking with me @Peachycoand for your initial response @binu_acs

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703