date_add_str.sql

Stored function which can do the same as MySQL's native DATE_ADD (and DATE_SUB), but differs in the way you invoke it: it accepts the units as a string instead of a keyword (p.e. 'DAY' instead of DAY)

Added: 2010-11-21 15:20:12

download

/******
* You may use and/or modify this script as long as you:
* 1. Keep my name & webpage mentioned
* 2. Don't use it for commercial purposes
*
* If you want to use this script without complying to the rules above, please contact me first at: marty@excudo.net
* 
* Author: Martijn Korse
* Website: http://devshed.excudo.net
*
* Date:  2010-11-21 15:20:12
***/

/**
 * DATETIME_ADD_STR
 */
CREATE FUNCTION `DATETIME_ADD_STR`(`datum` DATETIME, `expr` TINYINT, `unit` VARCHAR(50))
	RETURNS datetime
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY INVOKER
	COMMENT ''
BEGIN
   CASE UCASE(unit)
       WHEN 'SECOND' THEN RETURN DATE_ADD(datum, INTERVAL expr SECOND);
       WHEN 'MINUTE' THEN RETURN DATE_ADD(datum, INTERVAL expr MINUTE);
       WHEN 'HOUR' THEN RETURN DATE_ADD(datum, INTERVAL expr HOUR);
       WHEN 'DAY' THEN RETURN DATE_ADD(datum, INTERVAL expr DAY);
       WHEN 'WEEK' THEN RETURN DATE_ADD(datum, INTERVAL expr WEEK);
       WHEN 'MONTH' THEN RETURN DATE_ADD(datum, INTERVAL expr MONTH);
       WHEN 'YEAR' THEN RETURN DATE_ADD(datum, INTERVAL expr YEAR);
       ELSE RETURN '0000-00-00';
   END CASE;
END


/**
 * DATE_ADD_STR
 */
CREATE FUNCTION `DATE_ADD_STR`(`datum` DATETIME, `expr` TINYINT, `unit` VARCHAR(50))
	RETURNS date
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY INVOKER
	COMMENT ''
BEGIN
   CASE UCASE(unit)
       WHEN 'SECOND' THEN RETURN DATE_ADD(datum, INTERVAL expr SECOND);
       WHEN 'MINUTE' THEN RETURN DATE_ADD(datum, INTERVAL expr MINUTE);
       WHEN 'HOUR' THEN RETURN DATE_ADD(datum, INTERVAL expr HOUR);
       WHEN 'DAY' THEN RETURN DATE_ADD(datum, INTERVAL expr DAY);
       WHEN 'WEEK' THEN RETURN DATE_ADD(datum, INTERVAL expr WEEK);
       WHEN 'MONTH' THEN RETURN DATE_ADD(datum, INTERVAL expr MONTH);
       WHEN 'YEAR' THEN RETURN DATE_ADD(datum, INTERVAL expr YEAR);
       ELSE RETURN '0000-00-00';
   END CASE;
END

/**
 * DATETIME_SUB_STR
 */
CREATE FUNCTION `DATETIME_SUB_STR`(`datum` DATETIME, `expr` TINYINT, `unit` VARCHAR(50))
	RETURNS datetime
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY INVOKER
	COMMENT ''
BEGIN
   RETURN DATETIME_ADD_STR(datum, (-1 * expr), unit);
END


/**
 * DATE_SUB_STR
 */
CREATE FUNCTION `DATE_SUB_STR`(`datum` DATETIME, `expr` TINYINT, `unit` VARCHAR(50))
	RETURNS date
	LANGUAGE SQL
	DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY INVOKER
	COMMENT ''
BEGIN
   RETURN DATE_ADD_STR(datum, (-1 * expr), unit);
END

download