2

I have been having trouble trying to convert a column from 'varchar' to 'money'. Apparently the default schema is wrong. When I try to save a field for PriceRule, this is the error I get:

SQL Error: Disallowed implicit conversion from data type varchar to data type money, 'PriceRule', column 'UnitPrice'. Use the CONVERT function to run this query.

Accordingly, I try to use the convert function to convert the field datatype.

In My Model:

public function beforeSave($options = array()){
    $UnitPrice = $this->data["PriceRule"]["UnitPrice"];
    $this->data["PriceRule"]["UnitPrice"] = 'CONVERT(money,$UnitPrice)';
}

This is the SQL it generates:

UPDATE [pricerule] 
SET    [pricepolicyid] = 1, 
       [producttypeid] = 1, 
       [unitprice] = 'CONVERT(money,$UnitPrice)', 
       [minquantity] = 1, 
       [maxquantity] = 4, 
       [modifiedby] = 1055, 
       [modifieddate] = '2016-08-16 11:18:11', 
       [active] = '1' 
WHERE  [pricerule].[priceruleid] = 62

This query does not run because the CONVERT function is created as a string, not a function.

I have been continuously trying to find work-arounds and have done endless digging on google and cannot seem to find anything helpful.

If anyone has a way to fix this, that would be great! Even an alternative, any help is awesome!

Thanks in advance!

Anthony
  • 233
  • 4
  • 14
  • 1
    Why not fix the datatype in your code? Then you don't have to wrestle with it. What happens if you leave this as text in your code and somebody puts in invalid data? It is just as important from the application side of things to use the right datatypes. – Sean Lange Aug 16 '16 at 16:42
  • @SeanLange How do I do that? – Anthony Aug 16 '16 at 16:46
  • I have no clue. I can't even spell PHP but trying to convert it on an update like that indicates the datatype in your code is a string. Surely there is a numeric datatype you could use. Maybe somebody with even a passing knowledge of php can help. – Sean Lange Aug 16 '16 at 16:47

1 Answers1

0

Just convert to money in php like this 

$UnitPriceInMoney = money_format('%.2n',$UnitPrice);

PHP money_format

Y. M.
  • 107
  • 9