[筆記] 以C# 取得 此年份的所有週、週數、每週起始日、結束日

最近在經手的某個專案當中，遇見了需要取得年度週別日期的需求。
經過一番查詢以及苦戰後，
用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

執行的結果如圖 :