如何在SQL Server 2005中生成一年的周范围? [英] How to generate week ranges for a year in SQL server 2005?

问题描述

朋友，

我必须将YEAR WEEKNUMBER STARTDATE ENDDATE值插入数据表(例如Weekrange).如果我过2011年

每周范围从``2011-03-28''到``2011-04-03''.(因为在我的数据库2010中，上周范围以``2011-03-27''结尾)

这样我必须生成52周.

我想编写一个只需要年份作为参数的存储过程.今年，我必须生成周范围并将其插入到我的表格中，如上所示.

我怎样才能做到这一点 ?

此致

jn

Hi friends,

I have to insert YEAR WEEKNUMBER STARTDATE ENDDATE values to a datatable( say weekrange). If I pass 2011 as year

WEEK RANGE STARTS FROM ''2011-03-28 '' TO ''2011-04-03''.(Because in my database 2010 last week range endds with ''2011-03-27'')

Like this I have to generate for 52 weeks.

I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.

how can I do this ?

Regards,

jn

推荐答案

您可以使用DATEPART来确定当前日期是星期几.下面是演示它的代码.

You can use DATEPART to determine what week the current date is. Below is a code that demonstrates it.

DECLARE @currentDayOfWeek AS INT
DECLARE @currentDate AS DATETIME
DECLARE @startDate AS DATETIME
SET @currentDayOfWeek = (DATEPART(dw, GETDATE()) - 1) * -1
SET @currentDate = CAST(CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/'
+ CAST(DAY(GETDATE()) AS VARCHAR(2)) + '/'
+CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS DATETIME)
SET @startDate = DATEADD(DW,@currentDayOfWeek,@currentDate)
SELECT YEAR(@currentDate) AS [YEAR],
DATEPART(WW, @currentDate) AS WEEK,
@startDate AS STARTDATE,
DATEADD(DW, 6, @startDate) AS ENDDATE

输出为

Output is

YEAR   WEEK   STARTDATE   ENDDATE
2011   13     03/20/2011  03/26/2011

请注意，您的周数是13，因为它计算的是全年的周数.如果您希望它按月而不是按年计算周数，则需要做一些减法运算.

这不是一个很好的解决方案，但是它可以让您抢先一步. :)

Note that you week is 13 because it computes for the week number for the whole year. You need to do a little subtraction if you want it to do the week numbers by month and not by year.

Not an elegant solution but it can give you a headstart on what you need to do. :)

这是链接
sriramjithendra.blogspot.com

here is the link
sriramjithendra.blogspot.com

这篇关于如何在SQL Server 2005中生成一年的周范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！