Clear Time function for MySQL

12 / Apr / 2012 by Gaurav Sharma 1 comments

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]

FOUND THIS USEFUL? SHARE IT

comments (1 “Clear Time function for MySQL”)

  1. rakaris

    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *