mssql中自己常用的几个自定义函数

 

1 IF OBJECT_ID (N ' dbo.Get_MonthLastDate ' ) IS NOT NULL
2 DROP FUNCTION dbo.Get_MonthLastDate
3   GO
4
5   CREATE FUNCTION Get_MonthLastDate( @Date DATETIME , @time VARCHAR ( 8 ))
6   RETURNs DATETIME -- 返回当月的最后一天
7 BEGIN
8 DECLARE @returnDate DATETIME
9 IF month ( @date ) < 12
10 SET @returnDate = convert ( DATETIME , convert ( VARCHAR ( 4 ), year ( @Date )) + ' - ' + convert ( VARCHAR ( 2 ), month ( @date ) + 1 ) + ' -1 ' + @time ) - 1
11 ELSE
12 SET @returnDate = convert ( DATETIME , convert ( VARCHAR ( 4 ), year ( @Date )) + ' -1-1 ' + @time ) - 1
13 RETURN @returnDate
14 END
15
16 GO
17
18 CREATE FUNCTION Get_ShortIdCode( @IdCard VARCHAR ( 18 ))
19 RETURNS VARCHAR ( 15 )
20 -- 18位身份证号变15位
21 BEGIN
22 RETURN LEFT ( LEFT ( @idCard , 6 ) +RIGHT ( @idCard , 10 ), 15 )
23 END

 

 

 

 

IF OBJECT_ID (N ' dbo.Trim ' ) IS NOT NULL
DROP FUNCTION dbo.Trim
GO

CREATE FUNCTION Trim( @s VARCHAR ( 100 ))
RETURNS VARCHAR ( 100 )
BEGIN
RETURN rTrim ( ltrim ( @s ))
END
GO

 

转载于:https://www.cnblogs.com/ByBull/archive/2010/12/14/1905877.html