Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server first specified day-of-week in month function


Updated: 21 December 2012

Use the scalar function FIRSTWEEEKDAY to return the first specified day of the week in any calendar month.
SELECT [wctFinancial].[wct].[FIRSTWEEKDAY](
  <@StartDate, datetime,>
 ,<@DayofWeek, nvarchar(4000),>)
specifies the month and year of the return value. FIRSTWEEKDAY returns the first @DayofWeek in the month and year of @StartDate. @StartDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
the day of the week to be returned. @DayofWeek can be specified using the numbers 1 – 7, where 1 is Sunday and 7 is Saturday, or by using the name of the day of the week (e.g. 'Monday') or the abbreviated name of the week (e.g. 'Mon') as controlled by the Windows Regional Settings on the Windows Server.
Return Type
·         @StartDate must be a valid date.
·         @StartDate only determines the month and year of the returned value.
·         @DayofWeek always understands 1 to mean Sunday and 7 to mean Saturday. The day of week name and abbreviated name are validated against the Windows Server Regional Settings and not the SQL Server language settings.
·         Use the LASTWEEKDAY function to calculate the last specified day of the week in any calendar month.
In this example we calculate the first Wednesday in January 2013.
SELECT wct.FIRSTWEEKDAY('2013-01-01',4) as [First Wednesday]
This produces the following result.
First Wednesday
2013-01-02 00:00:00.000

If we wanted to calculate the 3rd Wednesday of the month, we can simply add 14 days or 2 weeks to the first Wednesday.
SELECT DATEADD(d,14,wct.FIRSTWEEKDAY('2013-01-01','Wednesday')) as [Third Wednesday]
SELECT DATEADD(ww,2,wct.FIRSTWEEKDAY('2013-01-01','Wednesday'))
This produces the following result.
Third Wednesday
2013-01-16 00:00:00.000
See Also
·         NBD - Create holiday string

Copyright 2008-2018 Westclintech LLC         Privacy Policy        Terms of Service