FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to calculate Monday's dates in a month?

Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to create a user-defined function for Monday dates calculation and test the UDF.

USE AdventureWorks;

GO

-- SQL user-defined function - SQL scalar function - UDF

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 = '1980-01-07' -- This is a Monday

    

    RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,

                  @FirstOfMonth)) / 7 * 7,

                   @SeedDate)

  END

GO

 

-- Test UDF

-- Third Monday in Feb, 2015

SELECT dbo.fnMondayDate(2015,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  

Exam Prep 70-461
Exam 70-461