Get week of *month* not year...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Get week of *month* not year...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-20 : 09:43:12

Chris Barr writes "Hi, this may be a simple question but it's hard for me to figure out. :) I searching your forums and looked in articles and didn't find an exact answer. What I need to do is get the week of the month (1, 2, 3, 4) depending on a records date. I'd like to do this in my sql statement. Currently my sql query is this: Please don't mind the table/column names, it's a standard here that I have to live with. Also, this query, it gives me the week of the year 1 - 52... How can I parse out the week of the month??????

SELECT events.cEventID as EventID, events.displayTitle as EventTitle, eventDates.EventDate,
Week = datepart("week", datepart("month", EventDate))
FROM tCalendarEvent_ events
INNER JOIN tCalendarEvent_Date eventDates
ON eventDates.cEventID = events.cEventID
WHERE month(EventDate) = 12 and year(EventDate) = 2001
ORDER by EventDate

sql 2000, w2k server..."

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-20 : 10:16:32

the issue you have to resolve 1st here, is what determines the start of a week (the 1st week) in a month....monday? saturday?, sunday? 1st? 1st working day? 1st week with more than 3/4 working days?...sort that out, and then it's just an issue of seeing how many days left in that "week"...if it's not 7....and adjusting for the numbers days into the month your target date is.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-20 : 11:52:45

The following is a little messy, but it might work. What you should do is find the week number for the date in question, and compare it to the week number of the first day of that month. Finding the difference between the two should provide what you're looking for:

SELECT eventDates.EventDate, FirstOfMonth=getDate()-Day(getdate())+1,
Week=Datepart(wk, EventDate)-DatePart(wk, getDate()-Day(getdate())+1)
FROM tCalendarEvent_Date eventDates

This should get you started, but it may not meet the requirements Andrew pointed out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources