MSSQL ENDOFMONTH Replacement

Workaround procedure for http://technet.microsoft.com/de-de/library/ee634239.aspx

CREATE VIRTUAL PROCEDURE views.endofmonth( IN getdate date ) returns (outdate date) as
BEGIN
 
    DECLARE date monthlater =  timestampadd( SQL_TSI_MONTH, 1, getdate );
    select  timestampadd( SQL_TSI_DAY, - dayofmonth(monthlater), monthlater);
 
END;;

Usage Examples

call views.endofmonth( cast ( '2012-02-07' as date ) )
 
result: 2012-02-29
 
call endofmonth( current_date )
 
result: Last day of current month
 
using in WHERE clause
 
SELECT foo from table.bar WHERE column = ( call endofmonth( current_date ) )

Articles in this section

Comments

Articles in this section

Related articles