T-SQL for UDFs

SQL Server has plenty of built-in functions, but sometimes we just have to combine some of them to create functions for our purposes. Such one, for example, is the function that returns the number of the days in a month. Before SQL Server 2012 we had to do that by combining more than two inbuilt functions and with SQL Server 2012+ versions it’s just a short one-line command.

This post shows seven ways for designing an example function. I’m sure there are some other interesting designs for the same function.

CREATE FUNCTION dbo.fnGetMonthDays1 ( @date date )
RETURNS int
AS
BEGIN
    DECLARE @isLeap int= 0;
    IF( YEAR(@date) % 400 = 0 OR ( YEAR(@date) % 4 = 0 AND YEAR(@date) % 100 != 0 ) )
    BEGIN
        SET @isLeap = 1
    END;
    DECLARE @month int= MONTH(@date);
    DECLARE @days int;
    SELECT @days = CASE
                   WHEN @month = 1 THEN 31
                   WHEN @month = 2 THEN 28 + @isLeap
                   WHEN @month = 3 THEN 31
                   WHEN @month = 4 THEN 30
                   WHEN @month = 5 THEN 31
                   WHEN @month = 6 THEN 30
                   WHEN @month = 7 THEN 31
                   WHEN @month = 8 THEN 31
                   WHEN @month = 9 THEN 30
                   WHEN @month = 10 THEN 31
                   WHEN @month = 11 THEN 30
                   WHEN @month = 12 THEN 31
                   END;
    RETURN @days;
END;

--Test
/*
SELECT dbo.fnGetMonthDays1(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays1(CONVERT(DATE,'2016-02-01'))
--29
*/

CREATE FUNCTION dbo.[fnGetMonthDays2] ( @date date )
RETURNS int
AS
BEGIN
    RETURN DAY(DATEADD(DAY, -1, DATEADD(MONTH, 1, CONVERT(date, @date, 101))));
END;

--Test
/*
SELECT dbo.fnGetMonthDays2(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays2(CONVERT(DATE,'2016-02-01'))
--29
*/

CREATE FUNCTION dbo.fnGetMonthDays3 ( @date date )
RETURNS int
AS
BEGIN
    RETURN DATEDIFF(d, DATEADD(month, DATEDIFF(month, 0, @date), 0), DATEADD(d, -1, DATEADD(m, 1, DATEADD(month, DATEDIFF(month, 0, @date), 0)))) + 1;
END;
GO
--Test
/*
SELECT dbo.fnGetMonthDays3(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays3(CONVERT(DATE,'2016-02-01'))
--29
*/

CREATE FUNCTION [dbo].[fnGetMonthDays4]
(
 @date  date
)
RETURNS INT
AS 
 BEGIN
    RETURN CASE
               WHEN MONTH(@date) IN(1,3,5,7,8,10,12) THEN 31
               WHEN MONTH(@date) IN(4,6,9,11) THEN 30
               ELSE CASE
                        WHEN(YEAR(@date)%4=0 AND --Leap Year
                             YEAR(@date)%100!=0) OR (YEAR(@date)%400=0) THEN 29
                        ELSE 28
                    END
           END;
END;
GO

--Test
/*
SELECT dbo.fnGetMonthDays4(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays4(CONVERT(DATE,'2016-02-01'))
--29
*/

CREATE FUNCTION [dbo].[fnGetMonthDays5] (@date date)
RETURNS int
AS
BEGIN
    RETURN DATEPART(dd, DATEADD(dd, DATEPART(dd, DATEADD(mm, 1, @date)) * -1, DATEADD(mm, 1, @date)));
END

--Test
/*
SELECT dbo.fnGetMonthDays5(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays5(CONVERT(DATE,'2016-02-01'))
--29
*/

CREATE FUNCTION [dbo].[fnGetMonthDays6] (@date date)
RETURNS int
AS
BEGIN
    RETURN DATEDIFF(dd, @date, DATEADD(mm, 1, @date));
END;

--Test
/*
SELECT dbo.fnGetMonthDays6(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays6(CONVERT(DATE,'2016-02-01'))
--29
*/

--Can be used from SQL Server 2012 onward
CREATE FUNCTION dbo.[fnGetMonthDays7] (@date date)
RETURNS int
AS
BEGIN
    RETURN DAY(EOMONTH(@date));
END
--Test
/*
SELECT dbo.fnGetMonthDays7(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays7(CONVERT(DATE,'2016-02-01'))
--29
*/

Rate

Share

Share

Rate