Mega Code Archive

 

Categories / MSSQL / Date Timezone

 

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>