星期五星期六结束时获取本周开始的数据 [英] Get data of current week start on saturday end in friday

问题描述

如何从星期六星期六结束的当周开始从sql server获取数据，所以选择本周的星期六到星期五的所有数据。



我发现这段代码但是在周日开始我无法改变它：

how can I get the data from sql server for current week starting in Saturday ending in Friday, so select all data from Saturday to Friday for the current week.

I found this code but started in Sunday and I can't change it:

SELECT TOP (200) id, p_id, p_name, s_quantity, s_p_price, s_price, cost_price, profit, date, note, p_delete FROM filters_sold
WHERE (date >= DATEADD(day, 0 - DATEPART(dw, GETDATE()), CONVERT(date, GETDATE()))) AND (date < DATEADD(day, 7 - DATEPART(dw, GETDATE()), CONVERT(date, GETDATE())))

我想要的是什么让我们说这个星期在哪里今天是星期日22/1/2017

i希望从星期六21/1/2017获取所有数据到2017年5月27日星期五。



那么当下周来的时候可以说下周六28/1/2017我想让当前的一周也开始2017年1月28日星期六2017年3月2日星期五结束时如此..



上面的代码从星期六到星期五得到数据但是一周的开始是星期天我怎么能到星期六



我尝试过：

what i want lets say where are in this week and today is Sunday 22/1/2017
i want to get all data from Saturday 21/1/2017 to Friday 27/1/2017.

then when the next week come lets say the next Saturday 28/1/2017 i want to get the current week also start in Saturday 28/1/2017 ending in Friday 3/2/2017and like so ..

the code above get the data from Saturday to Friday but the beginning of the week is Sunday how can i make it to Saturday

What I have tried:

<pre lang="SQL">SELECT TOP (200) id, p_id, p_name, s_quantity, s_p_price, s_price, cost_price, profit, date, note, p_delete FROM filters_sold
WHERE (date >= DATEADD(day, 0 - DATEPART(dw, GETDATE()), CONVERT(date, GETDATE()))) AND (date < DATEADD(day, 7 - DATEPART(dw, GETDATE()), CONVERT(date, GETDATE())))

推荐答案

I认为问题出在字段类型中。如果是datetime，它将包含一个时间值，因此您需要过滤日期大于前一天结束的位置。否则时间值将导致所有星期六日期被排除



例如

day>上周五和周日<下个星期六



ie

I think the problem is in the field type. If it is datetime it will include a time value and hence you will need to filter where the date is greater than the end of the day before. Otherwise the time value will cause all the saturday dates to be excluded

eg
day > last friday and day < next saturday

ie

date > dateadd(day, - datepart(dw,getdate()) -1, getdate()) and Date < dateadd(day, 7 - datepart(dw,getdate()), getdate()) 

不确定我的答案是否有助于您的查询，但...... 。这个



根据你的选择设定你的第一天。 us_english的默认设置是7（星期日）



只需设置查询



SET DATEFIRST 1;



价值一周的第一天是1星期一2星期二3星期三4星期四5星期五6星期六7（默认为美国英语）星期日



谢谢

Not sure whether my answer will help your query but ....try this

Set your first day of week according to your choice. The default setting for us_english is 7 (Sunday)

Just set in query

SET DATEFIRST 1;

Value First day of the week is 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday 7 (default, U.S. English) Sunday

Thanks

这篇关于星期五星期六结束时获取本周开始的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！