Showing posts with label month end. Show all posts
Showing posts with label month end. Show all posts

Friday, February 16, 2007

Get Month End in SQL Server

SQL Server doesn't have a function to get the month end. Here shows one I wrote:

 ALTER  function dbo.sp_getmonthend ( @inputDate    DATETIME )
 /*
   This function returns the month end of a specific date.
 */
 RETURNS DATETIME
 BEGIN
     DECLARE @outputDate        DATETIME

     select @outputDate = CAST(YEAR(@inputDate) AS VARCHAR(4)) + '-' +
                        CAST(MONTH(@inputDate) AS VARCHAR(2)) + '-01'
     select @outputDate = dateadd( day, -1, dateadd( month, 1, @outputDate ) )
     return @outputDate
 END
Since there is 1st in every month, we forward 1 month from 1st of the month and backward a day.