-- SQL user-defined function -- SQL scalar function – UDF CREATE FUNCTION fnMondayDate ( @Year INT, @Month INT, @MondayOrdinal INT ) RETURNS DATETIME AS BEGIN DECLARE @FirstDayOfMonth CHAR(10), @SeedDate CHAR(10) SET @FirstDayOfMonth = convert(VARCHAR, @Year) + '-' + convert(VARCHAR, @Month) + '-01' SET @SeedDate = '1900-01-01' RETURN DATEADD(DD, DATEDIFF(DD, @SeedDate, DATEADD(DD, (@MondayOrdinal * 7) - 1, @FirstDayOfMonth)) / 7 * 7, @SeedDate) END GO -- Test Datetime UDF -- Third Monday in Feb, 2015 SELECT dbo.fnMondayDate(2016, 2, 3) -- 2015-02-16 00:00:00.000 -- First Monday of current month SELECT dbo.fnMondayDate(Year(getdate()), Month(getdate()), 1) -- 2009-02-02 00:00:00.000
Source: http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
Last Updated on October 26, 2015