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