同一个函数MySql和SqlServer创建语法区别

最新推荐文章于 2024-08-08 10:53:57 发布

LtMamba

最新推荐文章于 2024-08-08 10:53:57 发布

阅读量131

点赞数

文章标签： mysql sqlserver

本文链接：https://blog.csdn.net/qq_37233070/article/details/120184338

版权

sqlserver：

CREATE FUNCTION [TimeSpenTODay]  -- 例如：020:56:34 转换为 0.8726 天
(@TimeString VARCHAR(20),@unit VARCHAR(20)='day')

RETURNS FLOAT
AS
BEGIN
DECLARE @hours float,
@mins float,
@seconds float,
@result FLOAT

SET @hours=convert(float,(LEFT(@TimeString,3)))
SET @mins=convert(float,(SUBSTRING(@TimeString,CHARINDEX(':',@TimeString)+1,2)))
SET @seconds=convert(float,(RIGHT(@TimeString,2)))

IF UPPER(@unit)='DAY'
 BEGIN 
  SET @result= @hours/24+@mins/1440+@seconds/86400;
 END
ELSE IF UPPER(@unit)='HOUR'
 BEGIN 
  SET @result= @hours+@mins/60+@seconds/3600;
 END
ELSE 
 BEGIN
  SET @result= @hours+@mins/60+@seconds/3600;
 END
RETURN ROUND(@result,4)
END
GO

Mysql：

create function TimeSpenTODay(TimeString varchar(20), unit varchar(20)) returns float
BEGIN
DECLARE hours float;
DECLARE mins float;
DECLARE seconds float;
DECLARE result FLOAT;

set unit='DAY';



SET hours=convert((LEFT(TimeString,3)),float);
SET mins=convert((SUBSTRING(TimeString,instr(':',TimeString)+1,2)),float);
SET seconds=convert((RIGHT(TimeString,2)),float);


IF UPPER(unit)='DAY' THEN
  SET result= hours/24+mins/1440+seconds/86400;
ELSEIF UPPER(unit)='HOUR' THEN
  SET result= hours+mins/60+seconds/3600;
ELSE
  SET result= hours+mins/60+seconds/3600;
END IF;
RETURN ROUND(result,4);
END;