MsSQL to MySQL function conversion

So I am trying to convert a function I created in MSSQL to MYSQL. The way I have it written in MSSQL is:

ALTER   function FormatDate(@date datetime) returns varchar(10)
begin
declare @salida varchar(10)

if (@date != '') and (@date != '01/01/1900')
    begin
        declare @day varchar(2)
        set @day = cast(day(@date) as varchar)
        if  len(@day) = 1 
                set @day = '0' + @day

        declare @month varchar(2)
        set @month = cast(month(@date) as varchar)
        if  len(@month) = 1 
                set @month = '0' + @month

        select @salida = @month + '/' + @day + '/' + cast(year(@date) as varchar)
    end
else 
    set @salida = null

return @salida
end

I am trying to convert that function into a MYSQL function. I tried this:

Delimiter $$

create function FormatDate(tiempo datetime) 
RETURNS varchar(10)
READS SQL DATA
BEGIN

declare salida varchar(10);

if ((tiempo != '') and (tiempo != '01/01/1900')) then 

        BEGIN

            declare dia varchar(2);

            set dia = cast(day(tiempo) as varchar);

            if  len(dia) = 1 then 
                set dia = '0' + dia;
            END IF;

            declare mes varchar(2);

            set mes = cast(month(tiempo) as varchar);

            if  len(mes) = 1 then 
                set mes = '0' + mes;
            END IF;

            select salida = mes + '/' + dia + '/' + cast(year(tiempo) 开发者_Go百科as varchar);




    else 

        set salida = null;

     END;  End if;

        return (salida);

END $$

Delimiter ; 

but I get an error when I try to execute that code.

This is the error I am getting:

Error Code: 1064. You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'varchar);

            if  len(dia) = 1 then 
          ' at line 14

• Can someone please help me convert this MSSQL function into a MYSQL function?

---

The function to determine a strings lenght in MySQL is called LENGTH(), not len()

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_length

MORE:

I added a working version of your function below. But please note that the built-in function DATE_FORMAT() does exactly what you want:

mysql> SELECT FormatDate( NOW() ), DATE_FORMAT( NOW(), "%m/%d/%Y" );
+---------------------+----------------------------------+
| FormatDate( NOW() ) | DATE_FORMAT( NOW(), "%m/%d/%Y" ) |
+---------------------+----------------------------------+
| 07/15/2011          | 07/15/2011                       |
+---------------------+----------------------------------+

You should either use it or replace your function body with a call of that function. Here is, however, a MySQL compatible version of your function:

DELIMITER $$

CREATE FUNCTION `FormatDate`(tiempo datetime) RETURNS varchar(10) 
    READS SQL DATA
BEGIN

    DECLARE salida VARCHAR(10);
    DECLARE dia VARCHAR(2);
    DECLARE mes VARCHAR(2);

    IF ( (tiempo <> '') AND ( tiempo <> '01/01/1900' ) ) THEN

        SET dia := CAST( DAY( tiempo ) AS CHAR );
        IF LENGTH( dia ) = 1 THEN
            SET dia := CONCAT( '0', dia);
        END IF;

        SET mes := CAST( MONTH( tiempo ) AS CHAR );
        IF LENGTH( mes ) = 1 THEN
            SET mes := CONCAT( '0', mes );
        END IF;

        SET salida := CONCAT_WS( '/', mes, dia, CAST( YEAR( tiempo ) AS CHAR ) );
    ELSE
        SET salida := NULL;
    END IF;

    RETURN salida;

    END $$

DELIMITER ;