CODEWITHSUNDEEP
mysqlcasting
aarona
aarona

Reputation: 37303

How to use the CAST function correctly in a MySql SELECT statement?

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?

Upvotes: 7

Views: 33137

Answers (1)

great_llama
great_llama

Reputation: 11729

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;

Upvotes: 15

Related Questions