问题描述
我想在SQL中计算上个月的月数。我有周数和年份。
I want to calculate Last Week Number of Month in SQL. I am having Week Number and Year.
例如如果我通过WeekNo = 51，Year = 2008，比函数应该返回LastWeekofMonth = 52。
Eg. If I pass WeekNo=51 , Year=2008 , than function should return LastWeekofMonth= 52.
我想使用以下标准计算周数。
I want to calculate Week number using below standards.
根据在瑞典使用的ISO 8601：1988，一年的第一周是新的一年中至少有四天的第一周。
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.
所以如果你的星期星期一从第一个星期四开始任何一年都在第一周内。您可以从中添加DateAdd或DateDiff。
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.
请帮助我..........
Please Help me..........
提前感谢
推荐答案
如果您使用 SQL Server ，则可以执行计算通过使用主表，而不创建日历表。 给你一个非常好的解释，我建议你阅读。用于计算每个月的第一个和最后一个星期日的SQL可以适应您的使用：
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)
编辑：一旦你有星期四的日期，你可以从这个日期获得这个星期的数字，如下所示：
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)
这篇关于如何在SQL中计算上个星期的MonthNO和Year的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！