示例：

date_add()函数：

DELIMITER |
create dba function gbasedbt.date_add(date_exp date, interval_int int, int_type varchar(25)) 
 returns date;
 define r_date date;
 if upper(int_type)=YEAR then
   select  (date_exp+interval_int units YEAR)  into r_date from systables where tabid=1;
 elif upper(int_type)=QUARTER then
   select  (date_exp+interval_int*3 units MONTH)  into r_date from systables where tabid=1;
 elif upper(int_type)=MONTH then
   select  (date_exp+interval_int units MONTH)  into r_date from systables where tabid=1;
 elif upper(int_type)=WEEK then
   select  (date_exp+interval_int*7 units DAY)  into r_date from systables where tabid=1;
 elif upper(int_type)=DAY then
   select  (date_exp+interval_int units DAY)  into r_date from systables where tabid=1;
 else
   select int_type into r_date from systables where tabid=1;
 end if;
 return r_date;
 end function;                                                                                                                                                                        
|

datetime_add()函数：

DELIMITER |
create dba function gbasedbt.datetime_add(date_exp datetime year to second, interval_int int, int_type varchar(25)) 
 returns datetime year to second;
 define r_date datetime year to second;
if upper(int_type)=YEAR then
   select  (date_exp+interval_int units year)  into r_date from systables where tabid=1;
 elif upper(int_type)=QUARTER then
   select  (date_exp+interval_int*3 units MONTH)  into r_date from systables where tabid=1;
 elif upper(int_type)=MONTH then
   select  (date_exp+interval_int units MONTH)  into r_date from systables where tabid=1;
 elif upper(int_type)=WEEK then
   select  (date_exp+interval_int*7 units DAY)  into r_date from systables where tabid=1;
 elif upper(int_type)=DAY then
   select  (date_exp+interval_int units DAY)  into r_date from systables where tabid=1;
 elif upper(int_type)=HOUR then
   select  (date_exp+interval_int units HOUR)  into r_date from systables where tabid=1;
 elif upper(int_type)=MINUTE then
   select  (date_exp+interval_int units MINUTE)  into r_date from systables where tabid=1;
 elif upper(int_type)=SECOND then
   select  (date_exp+interval_int units SECOND)  into r_date from systables where tabid=1;
 else
   select int_type into r_date from systables where tabid=1;
 end if;
 return r_date;
 end function;                                                                                                                                                                                                                                                   
|

注册：

将sql语句写入udr.sql文本里，执行dbaccess客户端工具注册date_add()和datetime_add()函数;

dbaccess db1 - < udr.sql

注意：事先保证数据库db1存在

测试：

一、DATE_ADD()和DATETIME_ADD()函数测试如下：

1.函数说明：

date_add函数可以实现一个时间点加减年、月、日、季度、星期的功能； datetime_add函数可以实现一个时间点加减时、分、秒的功能； date_add(Ymd date,interval_int int,mode varchar)函数有三个参数， Ymd代表一个时间格式为%Y-%m-%d,如2020-07-09； interval_int代表需要加上或者减去的时间间隔，正数代表加上，负数代表减去 mode代表需要加上或者减去的单位，有YEAR、MONTH、QUARTER、DAY、QUARTER、WEEK。 datetime_add(YmdHMS date,interval_int int,mode varchar)函数有三个参数， YmdHMS代表一个时间格式为%Y-%m-%d %H:%M:%S,如2020-07-09 12:00:00； interval_int代表需要加上或者减去的时间间隔，正数代表加上，负数代表减去 mode代表需要加上或者减去的单位，有HOUR、MINUTE、SECOND。

2.以下分别为测试年、月、日、季度、星期、时、分、秒用例

execute function date_add(2020-07-09, -2, YEAR);

execute function date_add(2020-07-09, -13, MONTH);

execute function date_add(2020-07-09, -366, DAY);

execute function date_add(2020-07-09, +2, QUARTER);

execute function date_add(2020-07-09, +6, WEEK);

execute function datetime_add(2020-07-09 12:00:00, +25, HOUR);

execute function datetime_add(2020-07-09 12:00:00, -10, MINUTE);

execute function datetime_add(2020-07-09 12:00:00, 61, SECOND);