如何在sql中获取上周的最后一天?
答
无论实际DATEFIRST设置的,最后一个周日可以发现这样的:
SELECT DATEADD(day,
-1 - (DATEPART(weekday, GETDATE()) + @@DATEFIRST - 2) % 7,
GETDATE()
) AS LastSunday
更换GETDATE()
用参数@date
获得特定日期之前的最后一个星期日。
答
SET @EndDate = GETDATE()-DatePart(dw, GETDATE());
+3
这不会返回正确的日期。现在运行它将返回上周六的4/30。 – IAmTimCorey 2011-05-08 00:04:16
答
这里是如何做到这一点有很大的文章:
http://www.objectreference.net/post/SQL-Find-last-week-date-range.aspx
你想使用@StartOfPrevWeek变量。
答
上周日(这是 “最后一周”结束)
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6) AS LAST_SUNDAY
本周(假设周一至周日周格式)
SELECT DATEADD(wk, DATEDIFF(wk, 7, CURRENT_TIMESTAMP), 7) AS START_OF_WEEK
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6 + 7) AS END_OF_WEEK
结果
倍START_OF_WEEK
-----------------------
2011-05-02 00:00:00.000
END_OF_WEEK
-----------------------
2011-05-08 00:00:00.000
的例子来解释巫术(使用这上面的SQL更改为需要从本周开始和周结束日的一周)一周的下方
- 例子找到天在本周内(周日至周六)
- 如果实际的END_OF_WEEK是下一个周日到周六,那么您需要+7到本周的值。 (见END_OF_WEEK上面的例子)
SQL下面
SELECT DATEADD(wk, DATEDIFF(wk, -2, CURRENT_TIMESTAMP), -2) AS DAY_OF_WEEK /* Saturday */
SELECT DATEADD(wk, DATEDIFF(wk, -1, CURRENT_TIMESTAMP), -1) AS DAY_OF_WEEK /* Sunday */
SELECT DATEADD(wk, DATEDIFF(wk, 0, CURRENT_TIMESTAMP), 0) AS DAY_OF_WEEK /* Monday */
SELECT DATEADD(wk, DATEDIFF(wk, 1, CURRENT_TIMESTAMP), 1) AS DAY_OF_WEEK /* Tuesday */
SELECT DATEADD(wk, DATEDIFF(wk, 2, CURRENT_TIMESTAMP), 2) AS DAY_OF_WEEK /* Wednesday */
SELECT DATEADD(wk, DATEDIFF(wk, 3, CURRENT_TIMESTAMP), 3) AS DAY_OF_WEEK /* Thursday */
SELECT DATEADD(wk, DATEDIFF(wk, 4, CURRENT_TIMESTAMP), 4) AS DAY_OF_WEEK /* Friday */
SELECT DATEADD(wk, DATEDIFF(wk, 5, CURRENT_TIMESTAMP), 5) AS DAY_OF_WEEK /* Saturday */
SELECT DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6) AS DAY_OF_WEEK /* Sunday */
SELECT DATEADD(wk, DATEDIFF(wk, 7, CURRENT_TIMESTAMP), 7) AS DAY_OF_WEEK /* Monday */
SELECT DATEADD(wk, DATEDIFF(wk, 8, CURRENT_TIMESTAMP), 8) AS DAY_OF_WEEK /* Tuesday */
SELECT DATEADD(wk, DATEDIFF(wk, 9, CURRENT_TIMESTAMP), 9) AS DAY_OF_WEEK /* Wednesday */
SELECT DATEADD(wk, DATEDIFF(wk, 10, CURRENT_TIMESTAMP), 10) AS DAY_OF_WEEK /* Thursday */
SELECT DATEADD(wk, DATEDIFF(wk, 11, CURRENT_TIMESTAMP), 11) AS DAY_OF_WEEK /* Friday */
SELECT DATEADD(wk, DATEDIFF(wk, 12, CURRENT_TIMESTAMP), 12) AS DAY_OF_WEEK /* Saturday */
etc...
答
对于合适的日历表,SQL更直接。没有巫术。
select max(cal_date) end_of_last_week
from calendar
where (cal_date < current_date and day_of_week = 'Sun');
end_of_last_week
--
2011-05-01
答
SELECT (DATEADD(DAY, ((DATEPART(dw, @Date) - 1) * -1), @Date))
答
DECLARE @LastSunday DATETIME
-- This will get the previous Sunday with time as 23:59:59
SELECT @LastSunday = Dateadd(SECOND, -1, Dateadd(WK, Datediff(WK, 6,
CURRENT_TIMESTAMP)
, 7))
SELECT @LastSunday
-- This gets the monday prior to it and time of 00:00:00
SELECT Dateadd(SECOND, 1, Dateadd(DAY, -7, @LastSunday))
-- This will make you time spans between eg, Monday 16/07/2012 00:00:00 through to Sunday 22/07/2012 23:59:59
-- Then use them in your WHERE clause like this
-- SELECT X,Y,Z From SomeTable
-- WHERE DateField BETWEEN @PreviousMondayToLastSunday AND @LastSunday
答
这将让你的未来和珍贵周五从给定的日期和时间
DECLARE @PREVIOUS int, @dtmStart datetime,@dtmEnd datetime, @NEXT int;
SET @dtmStart = '12/10/2013';
SET @dtmEnd = '12/11/2013';
select @PREVIOUS = datepart(dw,@dtmStart)
WHILE @PREVIOUS <> 6
BEGIN
SET @dtmStart = DATEADD(day , -1 ,@dtmStart)
SET @PREVIOUS = datepart(dw,@dtmStart)
CONTINUE
END
select @dtmStart
SELECT @NEXT = DATEPART(dw, @dtmEnd)
WHILE @NEXT <> 6
BEGIN
SET @dtmEnd = DATEADD(day , 1 ,@dtmEnd)
SET @NEXT = datepart(dw,@dtmEnd)
CONTINUE
END
select @dtmEnd
答
为了得到一个星期日,还是今天,如果今天是星期天,试试这个
DATEADD(day,- (DATEPART(dw,getdate()) + @@DATEFIRST -1) % 7, getdate())
答
这里是获取上一个星期六的日期的代码唉。此方法独立于数据库的设置。
declare @lastSaturday date,
@today date,
@todayName varchar(20);
select @todayName = datename(weekday, getdate()), @today = getdate();
select
case @todayName
when 'Saturday' then @today
when 'Sunday' then dateadd(day,-1,@today)
when 'Monday' then dateadd(day,-2,@today)
when 'Tuesday' then dateadd(day,-3,@today)
when 'Wednesday' then dateadd(day,-4,@today)
when 'Thursday' then dateadd(day,-5,@today)
when 'Friday' then dateadd(day,-6,@today)
end as LastSaturday;
哇,上周我想出了在给定日期后的下一个星期五找到模数技巧时,我觉得我很聪明。先生,我向你致敬。对于非数学爱好者来说,Andriy正在使用余数来返回星期天以来的天数。我给了一个大拇指! – 2011-05-09 21:21:56