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

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 ;

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:

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;
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 comment -