当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - How can I select records (containing Dates) grouped by Week

问题描述:

I have a table containing a field of dates + a field named WeekDayID (sun=0, mon=1, tue=2, wed=3, thu=4, fri=5, sat=6) + some other field (not important to mention), and I need two reports:

1.based on month

2.based on week

I have done the report number 1

my question is HOW CAN I ACHIEVE report 2, I need something that gives me this:


let's say we have dates from 2001/01/01 to 2001/01/31

I want to get all days grouped by week for example if 2001/01/01 is Sunday i want groups like:

1st week : 2001/01/01 - 2001/01/02 - ... - 2001/01/07

2nd week : 2001/01/08 - ... - 2001/01/14

3rd week : 2001/01/15 - ... - 2001/01/21

4th week : 2001/01/22 - ... - 2001/01/28

5th week : 2001/01/29 - 2001/01/30 - 2001/01/31

网友答案:

For the week number you can use the Datepart function with the value 'ww' for the first parameter:

SELECT Datepart(ww, myDate) FROM myTable

For the date range, you can do some math tricks: Substract to your date the weekday to get the first day of the week:

SELECT DateAdd(day, - DatePart(weekday, myDate), myDate) Week_Start
from myTable

here you have a link to the DatePart function:

http://msdn.microsoft.com/es-es/library/ms174420.aspx

分享给朋友:
您可能感兴趣的文章:
随机阅读: