create function dbo.tvf_EOM ( @iDate datetime)returns tableas return select dateadd(mm, datediff(mm, 0, @iDate) + 1, -1) as EndOfThisMonth, datename(dw,dateadd(mm, datediff(mm, 0, @iDate) + 1, -1)) as DatenameEndOfThisMonth;goselect dateadd(dd, t.N - 1, 0) as TheDate, EndOfThisMonth, DatenameEndOfThisMonthfrom dbo.Tally t cross apply dbo.tvf_EOM(dateadd(dd, t.N - 1, 0))where t.N between datediff(dd, 0, getdate()) and datediff(dd, 0, getdate()) + 120;

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),Casedatepart(dw, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))When 0 Then 'Sunday'when 1 then 'Monday'When 2 then 'Tuesday'when 3 Then 'Wednesday'When 4 Then 'Friday'When 5 Then 'Saturday'End as Day

--===== Create and populate a 1,000,000 row test table. -- Column "RowNum" has a range of 1 to 100,000 unique numbers -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers -- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings -- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers -- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times -- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' -- for all rows. -- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F) -- Jeff Moden SELECT TOP 1000000 SomeID = IDENTITY(INT,1,1), SomeInt = ABS(CHECKSUM(NEWID()))%50000+1, SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65) + CHAR(ABS(CHECKSUM(NEWID()))%26+65), SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)), SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY), SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME), SomeHex12 = RIGHT(NEWID(),12) INTO #JBMTest FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2 --===== Add a primary key just because ALTER TABLE #JBMTest ADD PRIMARY KEY CLUSTERED (SomeID)

CREATE FUNCTION dbo.LastDayOfMonth (@AnyDate DATETIME)RETURNS DATETIME AS BEGIN RETURN DATEADD(mm,DATEDIFF(mm,-1,@AnyDate),-1) END

SET NOCOUNT ONDECLARE @BitBucket DATETIME  PRINT '========== Inline method ==========' SET STATISTICS TIME ON SELECT @BitBucket = DATEADD(mm,DATEDIFF(mm,-1,SomeDate),-1) FROM #JBMTest SET STATISTICS TIME OFF  PRINT '========== UDF method ==========' SET STATISTICS TIME ON SELECT @BitBucket = dbo.LastDayOfMonth(SomeDate) FROM #JBMTest SET STATISTICS TIME OFF

========== Inline method ========== SQL Server Execution Times: CPU time = 1172 ms, elapsed time = 4311 ms.========== UDF method ========== SQL Server Execution Times: CPU time = 80047 ms, elapsed time = 147857 ms.