UDF problems

Databases
1

This is my first UDF ever. My goal is to take a date in the form ‘YYYY-MM-DD HH:MM AM’ and turn it into a UNIX timestamp. I am migrating data from a table that stores dates like this in the mediumtext, to a table that stores the date in unix timestamps.

Anybody have any idea why I keep getting:

#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 ‘’ at line 3

Here’s my UDF:

DELIMITER $$

CREATE FUNCTION CONVERT_PHP_TIMESTAMP(field MEDIUMTEXT)
RETURNS INT(11)
BEGIN
DECLARE field_date VARCHAR(45);
DECLARE field_hour VARCHAR(45);
DECLARE field_minutes VARCHAR(45);
DECLARE field_timestamp INT(11);

IF SUBSTRING(field, -2) = 'AM' THEN
	SET field_timestamp = SUBSTRING(field, -2);
ELSEIF SUBSTRING(field, -2) = 'PM' THEN
	SET field_date = SUBSTRING(field FROM 0 FOR 10);
	SET field_hour = SUBSTRING(field FROM 11 FOR 2);
	SET field_minutes = SUBSTRING(field FROM 14 FOR 2);
	SET field_hour = CONVERT(INT, field_hour);
	SET field_hour = (field_hour + 12);
	SET field_timestamp = CONCAT(field_date,' ', field_hour, ':', field_minutes);
END IF;

RETURN(SELECT unix_timestamp(field_timestamp));

END
$$

DELIMITER ;

2

i don’t think you have to do all that substring business, in fact i’m not sure a UDF is even needed

use UNIX_TIMESTAMP(STR_TO_DATE(textfld,‘%y-%m-%d %h:%i %p’))

3

Thanks for pointing me in the right direction. Your answer was close, but I needed to tweak it a bit to UNIX_TIMESTAMP(STR_TO_DATE(textfld,‘%Y-%m-%d %h:%i %p’)). Thanks for saving me from doing something much harder and unneeded.:smiley: