Hi all,
I am getting an error: Type mismatch. Number provided where a String is required when I am trying to perform the following:
[COL1] = Type is double
[COL2] = the new column this is creating, Type is V_WString
IF IsNull([COL1]) THEN Null() ELSEIF Length([COL1]) = 8 THEN LEFT([COL1], 4) + "-" + Substring([COL1], 5,2) + "-" RIGHT(COL1], 2)) ELSE DateTimeFormat(DateTimeAdd("1899-12-30", [COL1], "days"), "%Y-%m-%d") ENDIF
Thanks for your help!
Solved! Go to Solution.
Hi JP02,
Right, left, length and substring function demand a text in the string parameter.
We can use the function tostring() to modify the [COL1] type, another way is to use a select tool before.
It should be like this.
IF IsNull([COL1]) THEN Null() ELSEIF Length(tostring([COL1])) = 8 THEN LEFT(tostring([COL1]), 4) + "-" + Substring(tostring([COL1]), 5,2) + "-" RIGHT(tostring(COL1]), 2)) ELSE DateTimeFormat(DateTimeAdd("1899-12-30", [COL1], "days"), "%Y-%m-%d") ENDIF
Best,
Fernando Vizcaino
Hi Fernando!
I am gong to try that right now! I kept staring at it, thinking there had to be a function that I could call to convert the number to a string. I tried the Select tool to convert earlier, but the DateTimeAdd seems to expect a number, and so I was hitting a wall. I will let you know what happens in a few minutes!
Thanks again.
JP
Worked!! Thanks Much!
JP
Hi, in my input file, the valueDate changes from time to time. In one report it is Double format while in another one it's V_String.
I used below formula and it still says "Number provided where a string is required."
if length(tostring([valuedate]))=8 then datetimediff(datetimeparse(LEFT(tostring([valueDate]),4)+SUBSTRING(tostring([valueDate]),5,2)+SUBSTRING(tostring([valueDate]),8,2) ,"%y%m%d"),[startDate],"days")+2
else tonumber([valueDate]) endif
where [startdate] is 1900-01-01
missed another elseif
if length(tostring([valuedate]))=8 then datetimediff( datetimeparse(LEFT(tostring([valueDate]),4)+SUBSTRING(tostring([valueDate]),5,2)+SUBSTRING(tostring([valueDate]),8,2) ,"%y%m%d"),[startDate],"days")+2
elseif length(trim(tostring([valuedate])))=10 then datetimediff(datetimeparse(LEFT(tostring(trim([valueDate])),4)+SUBSTRING(tostring(trim([valueDate])),5,2)+SUBSTRING(tostring(trim([valueDate])),8,2) ,"%y%m%d"),[startDate],"days")+2
else tonumber([valueDate]) endif
So does that mean the TRIM function should also follow a string?