显示整个星期的开始日期为星期六
问题描述:
我已经在一年中的所有星期在这个网站的代码如下,我应该填充星期日期,开始日期为星期六和结束日期为星期五。当一周结束时,它应该进入下一周的日期。 我怎么能达到这个请帮助我。显示整个星期的开始日期为星期六
DECLARE @Year INT=2013;
DECLARE @start DATE;
--DECLARE @WK INT=2
SET @start = DATEADD(YEAR, @Year-1900, 0);
;WITH n AS
(
SELECT TOP (366) -- in case of leap year
TDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1, @start)
FROM sys.all_objects
),
x AS
(
SELECT md = MIN(TDate) FROM n
WHERE DATEPART(WEEKDAY, TDate) = 7 -- assuming DATEFIRST is SATURDAY
),
y(TDate,wk) AS
(
SELECT n.TDate, ((DATEPART(DAYOFYEAR,n.TDate)-
DATEDIFF(DAY, @start,x.md)-1)/7)+1
FROM n CROSS JOIN x
WHERE n.TDate >= x.md
AND n.TDate < DATEADD(YEAR, 1, @start)
)
SELECT [date] = TDate, [week] = wk
FROM y WHERE wk < 53
ORDER BY [date];
答
不是真的知道你是问什么,但根据您的查询上面这将给本周的数字,根据上周六是一周的第一天,2013年:
DECLARE @Year INT=2013;
DECLARE @start DATE;
SET @start = DATEADD(YEAR, @Year-1900, 0);
SET DATEFIRST 6; -- Set start of week as Saturday
WITH n AS
(
SELECT TOP (366) -- in case of leap year
TDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1, @start)
FROM sys.all_objects
)
select TDate
, DATEPART(WEEK,TDate)
from n
where year(TDate) = 2013;
编辑:
因此,基于各种评论和答案我认为什么这里需要,对于给定的一天,返回所有天同一周,周六是一周的第一天。因此,像这样:
set datefirst 6; -- make sure first day of week is Saturday
declare @date date = getdate(); -- change date as required here
with daysOfWeek as
(
select [date] = dateadd(dd, -datepart(dw, @date) + 1, @date)
union all
select [date] = dateadd(dd, 1, [date])
from daysOfWeek
where [date] < dateadd(dd, -datepart(dw, @date) + 7, @date)
)
select [date], dayOfWeek = datename(dw, [date])
from daysOfWeek
其中给出的结果:
我觉得这是怎么在这里需要?
第二个编辑:
首先,创建功能:
create function dbo.weekDates (@date date)
returns @dates table ([date] date, [dayofweek] varchar(9))
as
begin
with daysOfWeek as
(
select [date] = dateadd(dd, -datepart(dw, @date) + 1, @date)
union all
select [date] = dateadd(dd, 1, [date])
from daysOfWeek
where [date] < dateadd(dd, -datepart(dw, @date) + 7, @date)
)
insert into @dates ([date], [dayofweek])
select [date], [dayOfWeek] = datename(dw, [date])
from daysOfWeek;
return
end
go
使用功能:
set datefirst 6 -- Set Saturday as first day of week
select * from dbo.weekDates (getdate()) -- Change input parameter as required
答
我已经实现了,以显示星期日期,但它显示下周我希望展示当前周日期,这里的开始日是星期六和结束日星期五
ALTER FUNCTION GetCurrentWeek()
RETURNS @TWeek TABLE (TWeek NVARCHAR(20))
AS
BEGIN
DECLARE @Year INT= DATEPART(YEAR,GETDATE());
DECLARE @start DATE;
SET @start = DATEADD(YEAR, @Year-1900, 0);
;WITH n AS
(
SELECT TOP (366) -- in case of leap year
TDate = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1, @start)
FROM sys.all_objects
),
x AS
(
SELECT md = MIN(TDate) FROM n
WHERE DATEPART(WEEKDAY, TDate) = 7 -- assuming DATEFIRST is SATURDAY
),
y(TDate,wk) AS
(
SELECT n.TDate, ((DATEPART(DAYOFYEAR, n.TDate)
- DATEDIFF(DAY, @start, x.md)-1)/7) + 1
FROM n CROSS JOIN x
WHERE n.TDate >= x.md
AND n.TDate < DATEADD(YEAR, 1, @start)
)
INSERT @TWeek
SELECT [date] = TDate
FROM y WHERE wk =DATEPART(wk, GetDate())
ORDER BY [date];
RETURN;
END
我不明白你的问题,你期望输出什么?你有没有考虑过使用[日历表](http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html)而不是复杂的查询?或者这是为了填充日历表? – Pondlife 2013-02-13 17:14:27