How to calculate Last Week of Month by WeekNO and Year in SQL

I want to calculate Last Week Number of Month in SQL. I am having Week Number and Year.

Eg. If I pass WeekNo=51 , Year=2008 , than function should return LastWeekofMonth= 52.

I want to calculate Week number using below standards.

According to ISO 8601:1988 that is used in Sweden the first week of the year is the first week that has at least four days within the new year.

So if your week starts on a Monday the first Thursday any year is within the first week. You can DateAdd or DateDiff from that.

Pl开发者_JAVA百科ease Help me..........

Thanks in advance.

---

SELECT WEEK(LAST_DAY(STR_TO_DATE('2008-51-Mon', '%x-%v-%a')));

Should do the trick for getting the last week number of month with MySQL :

I first convert to a date, then I get the last day of the month (here: 2008-12-31), then I compute the week of the last day of the month (52).

It should be easy to turn it into a function.

Hope this helps.

---

This is fairly straightforward if you use a calendar table. The month you need is given by this query.

select iso_year, month_of_year
from calendar c
where iso_year = 2008 and iso_week = 51
group by iso_year, month_of_year
--
iso_year  month_of_year
2008      12

So you can use that result in a join on the calendar table, like this.

select max(c.iso_week) as last_week_of_month
    from calendar c
    inner join 
        (select iso_year, month_of_year
         from calendar c
         where iso_year = 2008 and iso_week = 51
         group by iso_year, month_of_year) m
    on m.iso_year = c.iso_year and m.month_of_year = c.month_of_year;
--
last_week_of_month
52

Here's one example of a calendar table, but it's pretty thin on CHECK constraints.

---

If you're using SQL Server, you can perform a calculation by using a master table, without creating a calendar table. This fellow gives you a very good explanation, which I recommend that you read. His SQL for calculating the first and last Sundays of each month can be adapted for your use:

declare @year int
set @year =2011 

select min(dates) as first_sunday,max(dates) as last_sunday from
(
select dateadd(day,number-1,DATEADD(year,@year-1900,0))
as dates from master..spt_values
where type='p' and number between 1 and
DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))
) as t
where DATENAME(weekday,dates)='sunday'
group by DATEADD(month,datediff(month,0,dates),0)

Edit: Once you have the date of the Thursday, you can get the week number from that date like this:

DECLARE @Dt datetime

SELECT @Dt='02-21-2008'

SELECT DATEPART( wk, @Dt)