why i am getting this conversion error...ConvError: Formula (157): DATETIMEDIFF1: "01/07/2

[Raj_007]

Hi All,

Thanks for your time. could someone please help.. i am missing something...what i have been doing is

1).I have a field in SQL server and its data type char(10) but has date values so in my select query i used CAST(MyDateField AS DATE)
2).In Alteryx - I have selected the datetimetool and selected the first option date/time format to string and selected MM/dd/yyyy
3).I have a calculated column like below..this is where it is showing conversion error - this is the formula 157

if DateTimeDiff([MyDateField1.Formated],DateTimeFormat(DateTimeToday(),'%m/%d/%Y'), "days")
then
1
else
0
endif

what is this am missing here? thanks a lot

[christine_assaad · Accepted solution]

Hi @Raj_007 

Use the ISO format "YYYY-MM-DD" to do any calculations in Alteryx and when you are done, you can convert the dates back to the needed format.

"Alteryx uses the ISO format yyyy-mm-dd HH:MM:SS to represent dates and times. If a DateTime value is not in this format, Alteryx reads it as a string. To convert a column for use and manipulation in the DateTime format, use the DateTimeParse function in the expression editor or the Date Time Tool"

https://help.alteryx.com/20213/designer/datetime-functions

Cheers!

[Raj_007]

Hi Christine, I am sorry.. could you please tell me where do i change it to YYYY-MM-DD 
if you look at the steps in my previous thread - in which step should i change it to

[Raj_007]

Big Thank you Christine - i got it working 

[christine_assaad]

Sorry @Raj_007 , did not see your message earlier but I'm glad you were able to figure it out. Thanks! 🙂