經過一番查詢以及苦戰後，
用C# 土法煉鋼的手法編出了符合ISO 8601的解決方法，
但在事後檢視的時候，經過同事建議我才發現在SQL當中，有著更輕鬆解決這個問題的方法呢。
以C#方式取得週數、週別、起始日期、結束日期
#region Week of the Year
#region Week of the Year
//依據今天日期 往前第 iSkip 周 ，取 iTake 周
public static List GetCurrentDateRangeList(int iSkip = 5, int iTake = 5)
{
string nowWeekofYear = GetCurrentWeekAndYear(DateTime.Now);
var allWeekList = GetWholeYearWeek();
var index = allWeekList.IndexOf(allWeekList.Where(x => x.weekYear == nowWeekofYear).FirstOrDefault()) + 1;
var currentList = allWeekList.Skip(index - iSkip).Take(iTake);
return currentList.ToList();
}
public static string GetCurrentWeekAndYear(DateTime time)
{
DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(time);
if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
{
time = time.AddDays(3);
}
return time.Year + "-" + CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}
//取得現去年+今年度的所有周別
public static List GetWholeYearWeek()
{
List li = new List();
li.AddRange(GetWeekList(DateTime.Now.Year - 1));
li.AddRange(GetWeekList(DateTime.Now.Year));
return li;
}
public static List GetWeekList(int iYear)
{
var jan1 = new DateTime(iYear, 1, 1);
//ISO 8601規範 禮拜一是每周的第 一天
var startOfFirstWeek = jan1.AddDays(1 - (int)(jan1.DayOfWeek));
var fullWeeks = Enumerable.Range(0, 54).Select(i => new { weekStart = startOfFirstWeek.AddDays(i * 7) }).TakeWhile(x => x.weekStart.Year <= jan1.Year).Select(x => new { x.weekStart, weekFinish = x.weekStart.AddDays(6) }).ToList();
//ISO 8601規範 每年最後一周，有多於四日在當年的為 新年度的第一周 或舊年度的最後一周 2019/12/31 (二) 該周則為2020的第一周 非2019的最後一周
if (fullWeeks.Last().weekStart.Year != fullWeeks.Last().weekFinish.Year)
{
var lastDTofYear = DateTime.Parse(fullWeeks.Last().weekStart.Year.ToString() + "/12/31");
DayOfWeek day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(lastDTofYear);
if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
{
// 該年12/31 在禮拜1~3 說明一月份在該周有四天以上 ，刪除最後一周
fullWeeks.Remove(fullWeeks.Last());
}
var fstweeklastDTofYear = DateTime.Parse(fullWeeks.First().weekStart.Year.ToString() + "/12/31");
day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(fstweeklastDTofYear);
if (day >= DayOfWeek.Thursday && day <= DayOfWeek.Sunday)
{
// 該年第一周的12/31 在禮拜4~日 說明一月份在該周有四天以上 ，刪除第一周
fullWeeks.Remove(fullWeeks.First());
}
}
var weeks = fullWeeks.SkipWhile(x => x.weekFinish < jan1.AddDays(1))
.Select((x, i) => new YearWeek
{
weekStart = x.weekStart,
weekFinish = x.weekFinish,
weekNum = i + 1,
weekYear = x.weekFinish.Year + "-" + (i + 1),
}).ToList();
return weeks;
}
#endregion /Week of the Year
public class YearWeek
{
public DateTime weekStart { get; set; }
public DateTime weekFinish { get; set; }
public string weekYear { get; set; }
public int weekNum { get; set; }
}
執行的結果如圖:
以SQL方式取得週數、週別、起始日期、結束日期
/************SQL************/
Declare @table(Date DATE, [Year] Nvarchar(10), [WeekNum] INT )
DECLARE @i INT = 1, @SDate DATE = N'2020/1/1'
WHILE @i<=366
BEGIN
INSERT INTO @table VALUES(@SDate, CONVERT(NVARCHAR, DATEPART(YEAR, @SDate)), CONVERT(NVARCHAR, DATEPART(WEEK, @SDate)))
SET @SDate = DATEADD(DAY, 1, @SDate)
SET @i = @i + 1
END
執行的結果如圖 :
