CODEWITHSUNDEEP
mysql
AnKing
AnKing

Reputation: 2174

CAST unsigned to signed int in MySql

I'm trying to select numbers from database stored as unsigned tinyint But on the output i need them to be negative.

This is what i do:

SELECT
 -CAST(sales_shipments_items.qty AS SIGNED INTEGER) AS qty,
FROM sales_shipments_items
WHERE 1

& this works on my local machine with mysql 5.5.25 But returns 0's on the server with mysql 5.5.32-cll-lve

BTW, on my local machine I do not even have to use CAST it works without it.

I tried to use CONVERT instead of CAST but still only get 0's

Sample data in sales_shipments_items

id|type|shp_num|qty
3   1   3321   2

4   1   3322   2

5   2   3321   2

6   3   3320   1

7   4   3350   1

8   5   3351   3

I'm expecting to see:

-2
-2
-2
-1
-1
-3

Upvotes: 4

Views: 13722

Answers (2)

Ibrahim Lawal
Ibrahim Lawal

Reputation: 1228

This works always. (MYSQL 5.6)

SELECT -1 * CAST(qty as SIGNED) as qty
FROM sales_shipments_items

Upvotes: 9

Hanky Panky
Hanky Panky

Reputation: 46900

Why complicate it? You can simply do:

SELECT (0 - qty) AS qty
FROM sales_shipments_items

Fiddle

As Jungsu noticed this does not work when your field is UNSIGNED, in that case you can run this query:

SELECT qty/-1 AS qty
FROM sales_shipments_items

Upvotes: 5

Related Questions