SQLTeam.com | Weblogs | Forums

Week no from Month


#1

Hi All,

I need to get the week no from the month. Please suggest how to do

Each month should be segregated like W1,W2,W3


#2

To my mind, that question doesn't even make sense. If all you have is a month, how can you work out the week?

Example: The month is January, what's the week?

If your working week is Monday to Sunday for example, and you are using calendar years, for any date in January your week number could be 52,1,2,3 or 4.

You're better off going more granular and starting off with the date, otherwise I don't see how you could do it.


#3

Lets assume date is available, you can use:

datepart(ww,date_field)

or

datepart(isoww,date_field)

If you absolutely need the W in front of weeknumber:

'W'+ltrim(str(datepart(ww,date_field)))

or

'W'+ltrim(str(datepart(isoww,date_field)))

#5

Bitsmed did what I would have suggested. It's pretty tough to beat the ISOWeek for its simplicity. There is a question that I have though. What do you want to do with the first week of January and the last week of December if they're not whole weeks? I ask because ISOWK may include a part of the previous year in the first week of January and may include a part of the next year in the last week of December.