6.3.5 Cast Functions

The syntax of the CAST function is:

CAST(expression AS type)

or

CONVERT(expression,type)

Where type is one of:

• BINARY
• CHAR (New in 4.0.6)
• DATE
• DATETIME
• SIGNED {INTEGER}
• TIME
• UNSIGNED {INTEGER}

CAST() is SQL-99 syntax and CONVERT() is ODBC syntax.

The cast function is mainly useful when you want to create a column with a specific type in a CREATE ... SELECT :

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);

CAST(string AS BINARY is the same thing as BINARY string. CAST(expr AS CHAR treats the expression as a string with the default character set.

NOTE: In MysQL 4.0 the CAST to DATE, DATETIME and TIME only marks the column to be a specific type but doesn't change the value of the column.

In MySQL 4.1.0 the value will be converted to the correct column when it's sent to the user:

mysql> SELECT CAST(NOW() AS date);
       -> 2003-05-26

You should not use CAST to extract data in different formats but instead use string functions like LEFT or EXTRACT(). See section 6.3.4 Date and Time Functions.

To cast a string to a numeric value, you don't normally have to do anything; just use the string value as it would be a number:

mysql> SELECT 1+'1';
       -> 2

If you use a number in string context the number will automatically be converted to a BINARY string.

mysql> SELECT CONCAT("hello you ",2);
       ->  "hello you 2"

MySQL supports arithmetic with both signed and unsigned 64-bit values. If you are using an numerical operations (like +) and one of the operands are unsigned integer, then the result will be unsigned. You can override this by using the SIGNED and UNSIGNED cast operators, which will cast the operation to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT CAST(1-2 AS UNSIGNED)
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
        -> -1

Note that if either operation is a floating-point value (In this context DECIMAL() is regarded as a floating-point value) the result will be a floating-point value and is not affected by the above rule.

mysql> SELECT CAST(1 AS UNSIGNED) -2.0
        -> -1.0

If you are using a string in an arithmetic operation, this is converted to a floating-point number.

The CAST() and CONVERT() functions were added in MySQL 4.0.2.

The handing of unsigned values was changed in MySQL 4.0 to be able to support BIGINT values properly. If you have some code that you want to run in both MySQL 4.0 and 3.23 (in which case you probably can't use the CAST function), you can use the following trick to get a signed result when subtracting two unsigned integer columns:

SELECT (unsigned_column_1+0.0)-(unsigned_column_2+0.0);

The idea is that the columns are converted to floating-point before doing the subtraction.

If you get a problem with UNSIGNED columns in your old MySQL application when porting to MySQL 4.0, you can use the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. Note however that as long as you use this, you will not be able to make efficient use of the UNSIGNED BIGINT column type.

User Comments

You should try:

SELECT ...., RAND() AS r ORDER BY r

Be aware of Unix_Timestamp. It is in localtime!
So daylight savingtime sensitive and thus, it is
not continouos!

hiya....

i was trying to use SELECT * FROM table_name ORDER BY RAND(), and it didn't work, then I realized that my crappy host has MySQL 3.22.30.

then I tryed to do something like "SELECT column, column2, column3, RAND() AS random FROM table ORDER BY random DESC LIMIT 20;" it doesn't work either (actually works, but it's not sorted)

is there any work around?

For random ordering on output, try:
SELECT ... ORDER BY RAND()

*sigh*. It would be REALLY nice if there was a
function that returns the interval between two
dates easily. But if you're trying to calculate
a current age from a birthdate, you can use:

DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS
(bdate)), '%Y')+0

for calculating ages, use following
statement:
YEAR(NOW())-YEAR(bdate)-
IF(MONTH(NOW())<MONTH(bdate),1,
IF(DAYOFMOTH(NOW())<DAYOFMONTH(bdate),1,0))

ORDER BY supports a numerical column reference
arg, so SELECT col_1, col_2, col_3, RAND() ORDER
BY 4 will work around your problem nicely. Very
handy for sorting on nameless expression columns.

I've tried out all the suggestions about ORDER BY
RAND()/random...
I get different random numbers, but still it's not
sorted, so I always get the same results at the
end. Is there any solution for this problem?!

ASP users: if you're getting empty recordset
returned when using +, add "OPTION=16384" to your
connectionstring, or check "Change Bigint to Int"
in the DSN manager!

Once again, everybody insists on making
everything harder than it should be.

If you are trying to match up a date - say you
want to automate a message announcing users'
birthdays - it is a non-date language construct
you need, and it is much shorter.

In PHP, my query looks like this:

SELECT username FROM login WHERE birthdate
LIKE '%-$month-$day'";

This assumes that you already have variables set
for $month and $day as I do, but if you don't,
then use the built-in MySQL date functions, %m, %
d, etc.

PHP and MySQL should be easy!

Say you have the year & week number (2002, 25)
Is there any functions you can get the Date of
Monday for that week ?

rand() works great for the given examples, but
doesn't work when you throw in a group by
clause:

select *
from product, category
where product.category_id =
category.category_id
group by category.category_id
order by rand()

I had hoped to get one random product in
each category. Instead, I get the same
product each time.
Tried fooling it by grouping by the
product.category_id as well.
Still such luck.
MySQL 3.23.41
YMMV

When doing a select on a float/decimal data
type, be AWARE that what you might think is
equal is not to mysql. Example, say you have a
datatype DECIMAL(12,31), then when you do an
equal to comparison, the number you pass in has
to have 31 decimal places it seems. Like if you
pass in 51.2, it would not return equal. But to
make this work, use ROUND(colname, 1) which will
round the number to 1 decimal place and then
your where condition will work.

Doesn't seem right since 51.2 is equal to
51.200000000000000....

I've also read that this happens on greater than
or less than statements too, so be careful.
http://www.geocrawler.com/archives/3/8/1999/5/0/2
173969/

To take the difference between two dates I use
these line of code in PHP program.

$sec1=exec("date -d \"2002-03-12 13:20:45\" +%s");
$sec2=exec("date -d \"2002-03-15 23:57:34\" +%s");

$diff=$sec1-$sec2;
$mm=$diff/60;
$hh=$mm/60;
$left_days=(int)($hh/24);

$left_hour=$hh%24;
$left_min=$mm%60;
$left_sec=$diff%60;

echo "$left_days Days,
$left_hour:$left_min:$left_sec"

For those who need to convert from (MM/DD/YYYY)
format to (YYYY/MM/DD the ansi standard used in
mysql) (it seems to have been rather rudely left
out) without using scripting languages to do so,
you can do this...

CONCAT( SUBSTRING_INDEX( MM-DD-YYYY-Date, '/', -
1) , '/', SUBSTRING_INDEX( MM-DD-YYYY-Date, '/',
1), '/', SUBSTRING_INDEX(SUBSTRING_INDEX(MM-DD-
YYYY-Date, '/',2), '/', -1))

Note, change the '/' to whatever deliminator you
are using in your date. This works well to
convert the date to the format that mysql will
understand and work properly with. To convert
from the ansi standard date format (YYYY/MM/DD)
back to (MM/DD/YYYY) use the DATE_FORMAT function.

The previous example for calculating
an age from a birthday does not work correctly.
This should work as expected, however it may be
better just to select the month, day and year and
do the arithmetic in your script rather than in
the query:

YEAR(NOW()) - YEAR(birthDate) - IF ( MONTH(NOW())
< MONTH(birthDate), 1, 0 ) - IF ( MONTH(NOW()) =
MONTH(birthDate) AND DAYOFMONTH(NOW()) <
DAYOFMONTH(birthDate), 1, 0)

I need a Integer field (mat_grupo) in SQL, but
not always I have this field available, then I
try to use:

CAST(0 as signed) as mat_grupo

to obtain it, but an error occurred after CAST
keyword. Where is the error? (excuse me by bad
English).

A very easy way to emulate date/time arithmetics
is to use the unix_timestamp() function, which
converts a mysql datetime to a unix timestamp
(number of seconds since 1970).

You can for instance compute the difference
between two dates by doing something like that:

select * from blah where ((unix_timestamp
(enddate)-unix_timestamp(startdate))/3600)>48;

this will select records where enddate-startdate
is more than 48 hours.

hope this helps..

-nd

How to insert date and time value to the data
base and How to retrive date and time from
database

So how do you change the precision of the result
of an expression... for example, when i divide two
INT's, it always rounds at the second place after
the decimal and i want it to the third place.

I am using MySQL 3.23 and thus don't have access to the CAST function. I have a string field that I wanted to be an integer. I got around this by using a simple calculation to force the result to be an integer. For example:

SELECT (string + 0) from examples;

Add your own comment.