MySQL —— to_char、to_date自定义函数
之前已分析 MySQL与Oracle在日期操作相关的差异
本文是用自定义函数实现to_char、to_date,此方法适用快速进行数据库兼容,减少代码的改动。
1、to_char
除日期外的类型转 字符串,可用convert(expr, char)或cast(expr as char)。其具体用法可参考 MySQL类型转换函数 —— binary、cast()、convert()
-- 此自定义函数只能解决 日期转字符串格式
-- DELIMITER $$ 定义结束符。MySQL默认的结束符是分号,但是函数体中可能用到分号。为了避免冲突,需要另外定义结束符。
DELIMITER $$
DROP FUNCTION IF EXISTS 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 $$
DELIMITER ;
2、to_date
-- TO_DATE
delimiter $$
drop function if exists 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 $$
delimiter ;
补充:
-- Oracle
TO_DATE(column, 'DD MONTH YYYY', 'NLS_DATE_LANGUAGE = American')
-- MySQL
str_to_date(column,'%d-%M-%Y')