23>
24> -- Creating a User-Defined Function
25>
26> /*
27~ Returns a date representing the last date
28~ of any given month.
29~ */
30> CREATE Function dbo.fn_LastOfMonth(@TheDate DateTime)
31> Returns DateTime
32> AS
33> BEGIN
34> DECLARE @FirstOfMonth DateTime
35> DECLARE @DaysInMonth Int
36> DECLARE @RetDate DateTime
37>
38> SET @FirstOfMonth = DATEADD(mm, DATEDIFF(mm,0,@TheDate), 0)
39> SET @DaysInMonth = DATEDIFF(d, @FirstOfMonth, DATEADD(m, 1, @FirstOfMonth))
40> RETURN DATEADD(d, @DaysInMonth - 1, @FirstOfMonth)
41> END
42> GO
1> select dbo.fn_LastOfMonth( getdate() )
2> GO
-----------------------
2006-10-31 00:00:00.000
(1 rows affected)
1> drop function dbo.fn_LastOfMonth;
2> GO
1>
2>