WeekDay Order in Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has a Date & Weekday Field and I need the get the Date
Order in Month. See example Below:

LockDown Date WeekDay DateOrderInMonth

7/25/07 WED 4 (Means 4th Wed
in July)
7/26/07 THU 3 (Means 3rd Thu
in July)
 
I have a table that has a Date & Weekday Field and I need the get the Date
Order in Month. See example Below:

LockDown Date WeekDay DateOrderInMonth

7/25/07 WED 4 (Means 4th Wed
in July)
7/26/07 THU 3 (Means 3rd Thu
in July)
Click to expand...

Presumably, this data is in an auxiliary table, typically named
Calendar, with one row for every day your application will ever need
(say, years 1990 to 2020). See:

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

You then use it like any other lookup table e.g. (aircode)

SELECT O1.OrderID, O1.CustomerID, O1.OrderDate,
C1.WeekDay, C1.DateOrderInMonth
FROM Orders AS O1
INNER JOIN Calendar AS C1
ON O1.OrderDate = C1.LockDownDate;

Jamie.

--
 
One way to determine the number is to use a simple formula

(Day(LockDownDate) -1) \ 7 + 1

That will return 1 to 5 for the dates. By the way 7/26/07 is the 4th
Thursday in the month


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top