SELECT CONVERT(varchar(10), DATEADD(d, (datediff(d,GETDATE(),created_date) /30)*30,GETDATE()),111)
FROM [DATA].[dbo].[expense]
group by CONVERT(varchar(10), DATEADD(d, (datediff(d,GETDATE(),created_date) /30)*30,GETDATE()),111)
這樣捉出來的資料如下:
2015/12/23
2015/10/24
2015/11/23
而我的專案是 ASP.Net MVC 5,所以有使用 mongoDB .Net Driver 2.2,一開始利用 LINQ寫法如下:
var query = fRepo.GetAll().GroupBy(x =>
DateTime.Now.AddDays(((int)(DateTime.Now.Subtract(DateTime.ParseExact(x.Date, "yyyy/MM/dd", System.Globalization.CultureInfo.InvariantCulture)).TotalDays) / Day30) * (-1 * Day30)).ToString("yyyy/MM/dd")
).Select(x => new DayPeriod { date1 =x.Key) }).OrderBy(x => x);