ToNumber "yyyy-mm-dd" Lost information in conversion & Conversion error Limit Reached

Hi Everyone, Hope everyone is doing well.

I have dataset which consists of around 800,000 records, and out of those hugh number of Records, THere is a column which contains "date" in Numeric format. like "43549" There are almost 800,000 records in the date column. Now I'm trying to change the format to Alteryx Default Date format.

i tried using ToDate() function, and datetimeadd(ToDate('1900-01-01'),ToNumber([Date Col]),

THis is running giving expected results in the Alteryx Desktop designer, But when I try to run on gallery it is throwing

- ToNumber "yyyy-mm-dd" Lost information in conversion

- Conversion error Limit Reached

Anyone has any idea how to resolve on the gallery side.

Please help.

Gallery

Date Time

Data Investigation

Accepted answers

All comments

cjaneczko

Try this.

DateTimeAdd("1900-01-01",[Date Col]-2,"days")

Raj

Use fromual
TOdate

as "43549" will be converted to "2019-03-25"

this will help.

Raj

ToDate(your field")

dkma

I know above formulas that., It is running on Desktop designer, but it is not working Gallery side it is throwing

dkma

I know above formulas that., It is running on Desktop designer, but it is not working Gallery side it is throwing

cjaneczko

Are there null or blank values in your dataset?

dkma

binu_acs

@dkma The error says you have some values in the format of yyyy-mm-dd, you can check the same in the designer using a filter tool

Contains([Field],'-')

dkma

@binu_acs @cjaneczko @Raj

Everyone, Thanks for your time and efforts.

https://knowledge.alteryx.com/index/s/article/Converting-strings-to-numbers-1583460179993?secureweb=Teams - this article helped me to fix

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703