Cannot convert string to datetime format
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Common Use Cases
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@IraWatt This is the initial one I tried. I get the error: ConvError: Select (3): Invoice Date: "INVOICE_DATE" is not a valid Date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Your suggestion also gave the error:
ConvError: Select (3): Invoice Date: "INVOICE_DATE" is not a valid Date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
