SQLUSA

Microsoft SQL Server 2005 Best Practices

How to calculate Monday's dates in a month?

 

Execute the following script in Query Editor to create a function which will calculate Monday's dates for a given year, month and ordinal:


USE AdventureWorks


CREATE FUNCTION fnMondayDate (@Year INT, @Month INT, @MondayOrdinal INT)
RETURNS datetime
AS
BEGIN
DECLARE @FirstOfMonth CHAR(10), @SeedDate CHAR(10)
SET @FirstOfMonth = convert(varchar,@Year)+'-'+convert(varchar, @Month)+'-1'
SET @SeedDate='1999-12-27'
RETURN DATEADD(DD,
DATEDIFF(DD,@SeedDate,
DATEADD(DD,(@MondayOrdinal * 7) -1, @FirstOfMonth) ) / 7 * 7,@SeedDate)

END
GO

SELECT dbo.fnMondayDate (2009, 2, 3)




 

SQLUSA - The Best SQL Server 2005 Training in the World
 
 
SQLUSA.com Home Page