Combine DateADD and DateDIFF to get the last date of a month : DATEDIFF « Date Timezone « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Date Timezone
3. DATEDIFF

Combine DateADD and DateDIFF to get the last date of a month

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>
           
       

Related examples in the same category