Cannot convert string to datetime format

[mystasz]

Hi Community,

I have a column that is in V_String format and looks like the below:

2018-07-31
2018-04-17
2018-07-26
2021-04-04
2018-04-05

I used the DateTime tool and selected String to Date/Time format. The custom logic I used is %Y-%m-%d.

I get the following error: 

ConvError: DateTime (38): Date_Out: Cannot convert "INVOICE_DATE" to a date/time with format "%Y-%m-%d" and language "English": Expected a number for year: 'INVOICE_DATE' Record #623054

ConvError: DateTime (38): Date_Out: Field Conversion Error Limit Reached

I've tried a bunch of combinations and am having no luck. 

[IraWatt]

Hey @mystasz,

The custom date format that your using I think is used in the formula functions. The Datetime tool uses other notation seen on the side here (arrow showing correct format):

I selected the one you want in the screen shot.

HTH,

Ira

[IraWatt]

Interestingly the string data you have is already in the Alteryx Date format so you can just use a select tool and set the column to a date

If you have any questions make sure to ask :) 

[DataNath]

Do you still have the same issue if you use a formula tool with the following?

DateTimeParse([INVOICE_DATE],"%Y-%m-%d")

From the looks of the error message, you have a strange input at Record #623054 and the message below suggests that there's a large number in there that pushes the output beyond the size of your data type.

[mystasz]

@IraWatt This is the initial one I tried. I get the error: ConvError: Select (3): Invoice Date: "INVOICE_DATE" is not a valid Date

[mystasz]

Tried this too and still got: 

ConvError: Select (3): Invoice Date: "INVOICE_DATE" is not a valid Date

ConvError: Select (3): Invoice Date: Field Conversion Error Limit Reached

[mystasz]

@DataNath 

Yes the data is about 2 million rows. Surprisingly, if I filter the SQL query, date has no issues formatting. 

Trying your solution now. Will report back. 

[mystasz]

@DataNath 

Your suggestion also gave the error:

ConvError: Select (3): Invoice Date: "INVOICE_DATE" is not a valid Date

[DataNath]

Looks like this may actually be a recurring issue when pulling in data resulting from SQL. My guess is that somewhere in your records (for example the record # Alteryx flagged up), there are dates not fitting the input format and therefore not allowing the formula to run. Could actually test this (I believe), by seeing if it still runs - but gives you some nulls - with the following:

IF DateTimeParse([INVOICE_DATE],"%Y-%m-%d") != DateTimeFormat([INVOICE_DATE],"%Y-%m-%d") then Null() else DateTimeFormat([INVOICE_DATE],"%Y-%m-%d") endif

In the screenshot below I've purposefully added an incorrect date to show where it may null when the incoming date format isn't correct.

[Qiu]

@mystasz 
I would suspect you have some illegal Date Format Data among the 2M records.
maybe we can do a conversion first then filter out those failed ones.