datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
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