Valid Date Range

[npeddagorla]

I have a date column with value '1000-01-01 00:00:00' in a DB when i tried to import that alteryx is marking it as not a valid date. What can i do in this case

[CharlieS · Accepted solution]

Wow, I didn't know this, but I was able to confirm Alteryx does not support dates before 1400-01-01. I used a Generate Rows tool to create a record for each day moving backwards from today to 1000-01-01 and got this error:

Ummm, move all your dates into the future by a set amount and do calculations that way maybe??

[danilang]

Hi @npeddagorla 

You can use a conversion function in the select statement to cast the field as a string in your Input tool.  Something like 

--TSQL

SELECT convert(varchar(25), date_field, 120) stringdate, field_1, field_2
From table1 

--Oracle

SELECT to_char(date_field, "YYYY-MM-DD HH24:MI:SS) stringdate, field_1, field_2
From table1 

This will import all the dates as strings.  Since the dates are in YYYY-MM-DD format, they'll sort in the same order whether they are string or date fields.  The only problem you'll have is if you have to do any date math.  You can use a process like this for dates before 1400

1. Extract the year form the string

2. Add 1000 to it

3. build a date field with the new year

4. do the math

5. extract the year and substract 1000

6. rebuild the date string.

Dan