# Calculate weekday number of a month

• ### Question

• Hi SQL-Experts,

searching the Internet I found some posts to get the week of month, for example

```Declare @myInputDate Date, @myOutputDate Date
Set @myInputDate = '04/01/2014'
Set @myInputDate = DATEADD(MONTH, 12 * DATEPART(Year, @myInputDate) - 22801 + DatePart(Month, @myInputDate), 5 - 1)
If DATEPART(WeekDay, @myInputDate) In (7, 1)
Begin
Set @myOutputDate = DATEADD(Day, (Case DATEPART(WeekDay, @myInputDate) When 7 Then 2 Else 1 End), @myInputDate)
End
Else
Begin
Set @myOutputDate = @myInputDate
End
Select DatePart(WeekDay, @myOutPutDate), @myOutputDate ```

or

`select cast(datename(week,getdate()) as int)-  cast( datename(week,dateadd(dd,1-day(getdate()),getdate())) as int)+1`

But I need the number of Weekday of the month. Today, on April 1st 2014 it will return 1, which is correct because it is the first Tuesday in this month.

On April 7th 2014 the above will return 2, but I want to get 1 because is the first Monday in this month.

Does anybody know a smart way to calculate the number of a special weekday in a month, like second monday, first Tuesday, fourth Sunday og April, Maiy .. by a given date.

It would be great to have a SQL Statement because I cant use (create) as StoredProcedure or Function here.

Regards,

Stefan

• Edited by Tuesday, April 1, 2014 12:47 PM typo in topic
Tuesday, April 1, 2014 12:46 PM

• Thanks, perhaps anybody knows or has a T-SQL Statement where I don't need to create procedures or tables ...

yep..you can use this

```--your date value passed
DECLARE @dt datetime
SET @dt='20140310'--pass any date here

SELECT (DATEPART(wk,@dt)-DATEPART(wk,DATEADD(mm,DATEDIFF(mm,0,@dt),0))) + CASE WHEN DATEDIFF(dd,0,@dt)%7 >= DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,@dt),0))%7 THEN 1 ELSE 0 END AS weekdaynumberformonth```

Tuesday, April 1, 2014 1:19 PM

### All replies

• This is where Calendar table is the most suitable candidate.

http://social.technet.microsoft.com/wiki/contents/articles/22776.t-sql-calendar-table.aspx

• Proposed as answer by Tuesday, April 1, 2014 12:57 PM
Tuesday, April 1, 2014 12:52 PM
• Latheesh NK, I have often proposed your solution as an answer, but this time it seems odd... :)

Chaos isn’t a pit. Chaos is a ladder. Many who try to climb it fail and never get to try again. The fall breaks them. And some are given a chance to climb, but they refuse. They cling to the realm, or the gods, or love. Illusions. Only the ladder is real. The climb is all there is.

Tuesday, April 1, 2014 12:58 PM
• Thanks, perhaps anybody knows or has a T-SQL Statement where I don't need to create procedures or tables ...

Tuesday, April 1, 2014 12:58 PM
• Thanks, perhaps anybody knows or has a T-SQL Statement where I don't need to create procedures or tables ...

yep..you can use this

```--your date value passed
DECLARE @dt datetime
SET @dt='20140310'--pass any date here

SELECT (DATEPART(wk,@dt)-DATEPART(wk,DATEADD(mm,DATEDIFF(mm,0,@dt),0))) + CASE WHEN DATEDIFF(dd,0,@dt)%7 >= DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,@dt),0))%7 THEN 1 ELSE 0 END AS weekdaynumberformonth```

Tuesday, April 1, 2014 1:19 PM
• I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is a separator token, ww is (01-53) week number and d is (1-7) day of the week.

You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'

There are several websites with calendars you can cut & paste, but you can start your search with: http://www.calendar-365.com/week-number.html

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Tuesday, April 1, 2014 1:20 PM
• Thanks a lot Visakh!

This works like a charm!

Tuesday, April 1, 2014 1:28 PM