Error: Type mismatch. Number provided where a String is required

[JP02]

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!

[fmvizcaino · Accepted 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

[JP02]

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

[JP02]

Worked!! Thanks Much!

JP

[JokeFun]

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

[JokeFun]

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?