Clear Time function for MySQL
Hi all,
Here is a simple function that can allow you to clear time from the DATETIME field of your database table
[sql]
DROP FUNCTION IF EXISTS cleartime;
delimiter //
CREATE FUNCTION cleartime(dt datetime) RETURNS DATETIME
NO SQL
DETERMINISTIC
BEGIN
DECLARE t varchar(15); — the time part of the dt
DECLARE rdt datetime default dt; — the datetime after time part is cleared
SET t = TIME(dt);
SET rdt = SUBTIME(dt,t);
RETURN rdt;
END //
delimiter ;
[/sql]
To import this function to your database just copy and paste above code in MySQL command prompt with your database selected.
The implementation is shown in following example:
[sql]
select cleartime(datetime_field) from table_name; — to view field with its time cleared
–OR
update table_name set datetime_field=cleartime(datetime_feild) from table_name;
[/sql]
Hi,
this does not work on Mysql 5.5.33-cll-lve, the following error is generated:
#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 ‘table name’ at line 1.
update table_name set datetime_field=cleartime(datetime_feild) from table_name;
When I run the query without “from table_name;”, query is executed but no row is updated.