Get Middle And End Of Month Dates T-SQL User Defined Function

Here is a simple T-SQL script which does the job.

USE YourDatabase
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + '   Server Name: ' 

+ @@servername + '   Service Name: ' + @@servicename
GO

IF EXISTS
    (SELECT *
    FROM sys.objects
    WHERE name = 'Middle_And_End_Of_Month_Dates')
    DROP FUNCTION YourSchema.Middle_And_End_Of_Month_Dates
PRINT 'DROP FUNCTION YourSchema.Middle_And_End_Of_Month_Dates'
GO

PRINT 'CREATE FUNCTION YourSchema.Middle_And_End_Of_Month_Dates'
GO

CREATE FUNCTION YourSchema.Middle_And_End_Of_Month_Dates(
/***********************************************************

Generates Middle And End Of Month Dates



Change Log

#    Date       Who  Change

***********************************************************/ 
@Start datetime)
RETURNS TABLE
AS
    RETURN(
            SELECT DISTINCT GeneratedDate = EOMONTH ( @Start + decimal# )
            FROM (
            SELECT row_number() OVER (ORDER BY (select 1)) as decimal#
            FROM sys.all_columns) tbl1
            UNION
            SELECT GeneratedDate = CONVERT(datetime, EOMONTH ( DateValue ), 121) - IIF(day(DateValue) < 30, 14, 15)
            FROM (
            SELECT DISTINCT DateValue = EOMONTH ( @Start + decimal# )
            FROM (
            SELECT row_number() OVER (ORDER BY (SELECT 1)) as decimal#
            FROM sys.all_columns) tbl2
            ) tbl3
)
/*******************************************************************
Test Sample:

SELECT * FROM EDW_atomic.YourSchema.Middle_And_End_Of_Month_Dates(
 '2015-12-31'    --@Start 
)
********************************************************************/
GO

If you find this script useful please donate generously.