How to use CAST function in foxpro

[Niki_S]

I used CAST function this way ,

SQLEXEC(lnSQL,[Cast(cPaymentNumber AS int) as Paynum], [MyTable])

but it says conversion failed when converting the varchar value'2345-7' into data type int.
What should I do for this?
Thank you

 

[Chris Miller]

CAST can convert the string '2345' to the number 2345, but it doesn't evaluate/calculate terms, so you'll not get 2338, if that's what you want. And you also won't get 23457, the payment number without a hyphen.

And once more, this isn't executed by VFP, your sending over code to an MSSQL Server.

If you want to have the digits only, then you have to use string functions to get only the digits and then cn finally CAST that to int.
What string functions have been teached to you in lessons leading to this?



Chriss

 

[Niki_S]

Here I have a char field and now I want to convert it into int field.

stra="select cBatchNo, dPaymentDate,cPaymentNumber from vACP_Payments "
SQLExec(hndOps,stra,'Cheque_no')

Now I did as below to convert char into int.

Select cBatchNo,dPaymentDate, Cast(cPaymentNumber As Numeric(10)) As nPymntNum  from Cheque_no into cursor ABC

Now I want to convert cPaymentNumber field into int field when I taking data from SQL.

 

[Chris Miller]

You've made your goal clear, but you didnÄt get the error message, it tells you you can't get a number from a string like '2345-7'

VFPs CAST is less strict, in VFP CAST()
but T-SQL cast will only cast values, that are a valid string representation of a number, neither a calculation nor a minus used as hyphen.

VFPs less strict CAST('2345-7' as int) results in 2345. While that's no error you still didn't say whether that's what you need or not.

If you already want to cast a payment number to int in SQL-Server with T-SQL there is no way to force CAST to work like VFPs CAST. You have to do work on the string to strip off unwanted characters like a minus in the middle of the number. Therefore you need string functions to act on cPaymentNumber to result in '23457' before that can be cast to the int 23457.



Chriss