locked
Query to find occurrance of day in month RRS feed

  • Question

  • User1143409625 posted

    Hi, I need a query to find the occurence of day in month.

    Date

    Day of Week

    Occurrence of day in month

    2011-05-01 00:00:00.000

    1

    1 (First Sunday)

    2011-05-02 00:00:00.000

    2

    1 (First Monday)

    2011-05-03 00:00:00.000

    3

    1

    2011-05-04 00:00:00.000

    4

    1

    2011-05-05 00:00:00.000

    5

    1

    2011-05-06 00:00:00.000

    6

    1

    2011-05-07 00:00:00.000

    7

    1

    2011-05-08 00:00:00.000

    1

    2 (Second Sunday)

    2011-05-09 00:00:00.000

    2

    2 (Second Monday)

    2011-05-10 00:00:00.000

    3

    2

    Monday, May 30, 2011 12:05 AM

Answers

  • User1143409625 posted

    I got the answer.

    SELECT [Date], DATEPART(WEEKDAY, [Date]) AS [Day], DAY([Date]) / 7 + 1 AS [Week] AS [Occurenance Of Weekday] FROM Table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 31, 2011 4:32 AM

All replies

  • User181256233 posted

    Do you want it to be a query based on a datetime value?

    Shouldn't 2010-05-01, 2010-05-02 and 2010-05-03 return 2 aswell? Or do you mean it should give the monday-counter ?

     

    Monday, May 30, 2011 1:27 AM
  • User1143409625 posted

    I need to find the Nth occurrence of a weekday in a month.

    Monday, May 30, 2011 2:08 AM
  • User181256233 posted

    If i query i first monday date it should give 1 ? and if i query the second monday of a month it should give 2 ? correct?

    Monday, May 30, 2011 2:11 AM
  • User1143409625 posted

    Yes. As shown in the table. Do u have any idea on it?

    Monday, May 30, 2011 2:25 AM
  • User181256233 posted
    Here is a simple extensionsmethod on DateTime:
    public static class DateTimeExtensions
    	{
    		public static int GetOccuranceForDate(this DateTime date)
    		{
    			var list = new Dictionary<DayOfWeek, List<int>>(DateTime.DaysInMonth(date.Year, date.Month));
    			for (var i = 1; i < DateTime.DaysInMonth(date.Year, date.Month); i++)
    			{
    				var tmpDate = new DateTime(date.Year, date.Month, i);
    				if (!list.ContainsKey(tmpDate.DayOfWeek))
    					list.Add(tmpDate.DayOfWeek, new List<int>());
    
    				list[tmpDate.DayOfWeek].Add(tmpDate.Day);
    			}
    			return list[date.DayOfWeek].IndexOf(date.Day) + 1;
    		}
    	}
    And simply use it like this:
    var current = DateTime.Now;
    Console.WriteLine(current.GetOccuranceForDate());
    Monday, May 30, 2011 2:44 AM
  • User1143409625 posted

    Ops. Thanks for your reply.

    I would like to have it in sql query.

    Monday, May 30, 2011 2:47 AM
  • User181256233 posted

    Oh sorry i missed what category this question was posted in :)

    Monday, May 30, 2011 3:04 AM
  • User1143409625 posted

    Its fine. I cracking my head to write the query.

    Monday, May 30, 2011 3:05 AM
  • User1143409625 posted

    I got the answer.

    SELECT [Date], DATEPART(WEEKDAY, [Date]) AS [Day], DAY([Date]) / 7 + 1 AS [Week] AS [Occurenance Of Weekday] FROM Table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 31, 2011 4:32 AM