mysql中to_char()和to_date()函数的实现

1.to_char()

-- mysql中to_char()和to_date()函数的实现
-- to_char() 函数的实现
create function to_char(d datetime, format varchar(40))
returns varchar(40) deterministic
begin
	declare str varchar(40) DEFAULT '';
	set str = replace(format, 'YYYY', '%Y');	
	set str = replace(str, 'yyyy', '%Y');	
	set str = replace(str, 'MM', '%m');	
	set str = replace(str, 'mm', '%m');	
	set str = replace(str, 'DD', '%d');	
	set str = replace(str, 'dd', '%d');	
	set str = replace(str, 'HH24', '%H');	
	set str = replace(str, 'hh24', '%H');	
	set str = replace(str, 'HH', '%h');	
	set str = replace(str, 'hh', '%h');	
	set str = replace(str, 'MI', '%i');	
	set str = replace(str, 'mi', '%i');	
	set str = replace(str, 'SS', '%s');	
	set str = replace(str, 'ss', '%s');	
	return date_format(d, str);
end;

2.to_date()

-- to_date() 函数的实现
create function to_date(s varchar(40), format varchar(40))
returns datetime deterministic
begin
	declare str varchar(40) DEFAULT '';
	set str = replace(format, 'YYYY', '%Y');	
	set str = replace(str, 'yyyy', '%Y');	
	set str = replace(str, 'MM', '%m');	
	set str = replace(str, 'mm', '%m');	
	set str = replace(str, 'DD', '%d');	
	set str = replace(str, 'dd', '%d');	
	set str = replace(str, 'HH24', '%H');	
	set str = replace(str, 'hh24', '%H');	
	set str = replace(str, 'HH', '%h');	
	set str = replace(str, 'hh', '%h');	
	set str = replace(str, 'MI', '%i');	
	set str = replace(str, 'mi', '%i');	
	set str = replace(str, 'SS', '%s');	
	set str = replace(str, 'ss', '%s');	
	return str_to_date(s, str);
end;


-- 参考：
-- Oracle
TO_DATE(column, 'DD MONTH YYYY', 'NLS_DATE_LANGUAGE = American')
-- MySQL
str_to_date(column,'%d-%M-%Y')