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.

1 comment:

Anonymous said...

Good words.