CAST Function
The MySQL cast function is used to mutate a value of one type (data type) to another type. In other words, the CAST() function takes a value of one type and produces a value of another type.
Syntax
The "Type" can have the following values:
- Binary
- Char
- Date
- DateTime
- Decimal
- Signed [integer]
- Time
- Unsigned [integer]
Create a table in MySQL
The following will create a table in MySQL:
Select table data from MySQL
The following uses MySQL to select table data:
Use cast function in MySQL
The following shows how to use a cast function in MySQL:
Example of CAST Function with PHP
The following is a sample use of the CAST Function with PHP; in the example, the CAST function converts a "DateTime" data type to a "DATE" data type.
<?php
$con=mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
print "<h2>MySQL: Simple Select statement</h2>";
$result = mysql_query("select * from mcnemployee");
echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>EmpId</th>
<th>EmpJoinDate</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['emp_id'] . "</td>";
echo "<td>" . $row['Fname'] . "</td>";
echo "<td>" . $row['Lname'] . "</td>";
echo "<td>" . $row['emp_JoinDate'] . "</td>";
echo "</tr>";
}
echo "</table>";
print "<h2>MySQL: With Cast Function</h2>";
$result = mysql_query("select CAST(emp_JoinDate as Date) As JoinDate from mcnemployee;");
echo "<table border='1'>
<tr>
<th>EmpJoinDate</th>
</tr>";+-
while($row = mysql_fetch_array($result))
{
echo "<td>" . $row['JoinDate'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
Output
CONVERT Function
The MySQL convert function is used to mutate a value of one type (data type) to another type. In other words, the CAST() function takes a value of one type and produces a value of another type. Basically the cost and convert functions do the same thing but there is a little difference between the cast and convert functions.
Difference between cast and convert function
CONVERT allows a greater breadth of flexibility when converting between date and time values and fractional numbers.
CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression.
Syntax
The "Type" can have the following values:
- Binary
- Char
- Date
- DateTime
- Decimal
- Signed [integer]
- Time
- Unsigned [integer]
Use Convert function in MySQL
Example of CONVERT Function with PHP
<?php
$con=mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
print "<h2>MySQL: Convert Function</h2>";
$result = mysql_query("select CAST(emp_JoinDate as Date) As JoinDate from mcnemployee;");
echo "<table border='1'>
<tr>
<th>EmpJoinDate</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<td>" . $row['JoinDate'] . "</td>";
echo "</tr>";
}
echo "</table>";
?>
Output