Oracle和mysql自定义函数

场景描述：根据要求判断当前时间属于那一天的工作量，如10:00:00之前就是前一天的工作量。

oracle

create or replace function get_date(v_datetime in date,v_time in varchar2) return varchar2 as

  v_date varchar2(50);
  beginDate varchar2(50);
  cDate varchar2(50);
begin
   cDate:= to_char(v_datetime, 'YYYY-MM-DD hh24:mi:ss');
   beginDate:=concat(to_char(v_datetime, 'YYYY-MM-DD '),v_time);
   if cDate<beginDate then
     v_date:=to_char(v_datetime-1, 'YYYY-MM-DD');
     else
       v_date:=to_char(v_datetime, 'YYYY-MM-DD');
   end if;
   return v_date;
exception
  when no_data_found then
    raise_application_error(-20001, '你输入的日期无效！');
end get_date;

示例

 select get_date(sysdate,'10:00:00') from dual

---

mysql

DROP FUNCTION IF EXISTS get_date;
CREATE FUNCTION get_date(v_datetime datetime,v_time VARCHAR(50)) 
RETURNS VARCHAR(50)
BEGIN
	DECLARE v_date VARCHAR(50);
  DECLARE cDate VARCHAR(50);
  DECLARE beginDate VARCHAR(50);
	set cDate= date_format(v_datetime, '%Y-%m-%d %H:%i:%s'); 
	set beginDate=concat(date_format(v_datetime, '%Y-%m-%d '),v_time); 
	if cDate<beginDate then
     set v_date=date_format(date_add(v_datetime,interval -1 day), '%Y-%m-%d');
   else
     set  v_date=date_format(v_datetime, '%Y-%m-%d');
  end if;
	RETURN v_date;
END

示例

select get_date(now(),'10:00:00')