I'm converting an MSSQL DB to MySQL DB and I have a stored procedure that is using a cast function to convert from a datetime datatype to a varchar datatype. Whether or not this matters in php/mysql since php isn't strongly typed (and I dont know if it would matter or not) I really want to keep the SP a close to the orginal as possible so I can maintain the same expected functionality. The problem is that I can't get the cast function to work right in mysql. Here is a test I tried that got me an error:
DELIMITER ;//
DROP PROCEDURE IF EXISTS `test`;//
CREATE PROCEDURE `test`()
BEGIN
SELECT CAST(my_table.DateColumn AS VARCHAR(10)) as TextColumn
FROM my_table;
END;//
What am I doing wrong?
The CAST() function in MySQL is used to convert a value from one data type to another data type specified in the expression. It is mostly used with WHERE, HAVING, and JOIN clauses. This function is similar to the CONVERT() function in MySQL. It converts the value into DATE datatype in the "YYYY-MM-DD" format.
The MySQL CAST() function is used for converting a value from one datatype to another specific datatype. The CAST() function accepts two parameters which are the value to be converted and the datatype to which the value needs to be converted.
You can cast data into BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED data types. mysql> select cast(1 as char) from sales; You can also use MySQL CAST in WHERE clause.
VARCHAR isn't a valid type for the CAST function, but CHAR is.
SELECT CAST(my_table.DateColumn AS CHAR(10)) as TextColumn FROM my_table;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With