Bir oyda har hafta uchun boshlash va tugash sanasini oling

Men ushbu oyda/yilda har hafta uchun boshlanish va tugash vaqtini olishim kerak. (oy va yil har doim - mart 2017 yilgi kabi beriladi).

Misol uchun, yanvar 2017:

1 week : '2017-01-01' - '2017-01-01'
2 week:  '2017-01-02' - '2017-01-08'
3 week:  '2017-01-09' - '2017-01-15'
4 week:  '2017-01-16' - '2017-01-22'
5 week:  '2017-01-23' - '2017-01-29'
6 week:  '2017-01-30' - '2017-01-31'

enter image description here

Men allaqachon bilishingiz mumkin berilgan oy/yil uchun haftalar soni:

select *,
    DATEDIFF(WEEK, DATEADD(day,-1,StartAt), DATEADD(day,-1,EndAt)) +1
    as NumWeeks

Biroq, har oyda/oyda boshlang'ich/tugash sanasini qanday qilib olish mumkin?

1
@ATC - SQL Server 2012.
qo'shib qo'ydi muallif FrenkyB, manba
@James - ha, bu to'g'ri.
qo'shib qo'ydi muallif FrenkyB, manba
Yil o'tishi kerakmi yoki yo'qmi?
qo'shib qo'ydi muallif SqlZim, manba
Birinchi haftangiz har doim 1 yanvarda boshlanadi.
qo'shib qo'ydi muallif James Z, manba
Panagiotis Kanavos siz ilgari surgan savolingizni sharhlab berganidek, taqvim jadvali barcha narsalarni osonlashtiradi. Ehtimol, ba'zi redesign tartibda.
qo'shib qo'ydi muallif Giorgos Altanis, manba
SQL serverining qaysi versiyasi?
qo'shib qo'ydi muallif ATC, manba

6 javoblar

Shunga o'xshash narsa?

DECLARE @Dates TABLE (DateId INT IDENTITY, Dt Date);

DECLARE @STart Date = DATEADD(Year, DATEDIFF(Year, 0, GETDATE()),0)

SET NOCOUNT ON;

WHILE @STart <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
BEGIN
        INSERT INTO @Dates (Dt) VALUES (@STart)

    SELECT @Start = DATEADD(DAY, 1, @STart)
END

Yangilangan

SELECT 
  DateId
, Dt
, DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0) AS WeekBeginningMondayOf
, DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0)) AS WeekEndingSundayOf
, DENSE_RANK() OVER(PARTITION BY MONTH(Dt) ORDER BY DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0)) AS WeekInMonth
FROM @Dates
1
qo'shib qo'ydi
Dushanbadan haftaning birinchi kuni bo'lishini o'zgartirib bo'ladimi?
qo'shib qo'ydi muallif FrenkyB, manba
Afsuski, uzr so'rayman. DATEAddDATEDIFF-ni amalga oshirganingizda unutdingizmi, har ikkala uchun to'g'ri haftani ishlatganda ba'zi g'alati narsalar mavjud. Yangilanganni ko'ring.
qo'shib qo'ydi muallif djangojazz, manba
select 
    DateValue
  , WeekStart =convert(date,(
    case when datepart(week,DateValue) =1 
      then convert(date, (datename(year,DateValue) +'0101')) 
      else dateadd(day,@@datefirst-datepart(weekday,DateValue)-(@@datefirst-1),DateValue)
      end) ) 
  , WeekEnd  =convert(date,(
    case when datepart(week,DateValue) =53 
      then convert(date, (datename(year,DateValue) +'1231')) 
      else dateadd(day,(@@datefirst)-datepart(weekday,DateValue)+([email protected]@datefirst),DateValue)
      end) ) 
from dates

rextester demo: http://rextester.com/KYKS44588

sinovni sozlash:

set datefirst 1;
declare @fromdate date = '20161227', @thrudate date = '20201231';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
    [DateValue]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo cross join n as [tenK] 
  order by 1
)
, test as (
  select 
      convert(varchar(10),DateValue,120) as Date
    , WeekStart = convert(varchar(10),convert(date,(
        case when datepart(week,DateValue) =1 
          then convert(date, (datename(year,DateValue) +'0101')) 
        else dateadd(day,@@datefirst-datepart(WeekDay,DateValue)-(@@datefirst-1),DateValue)
        end) ),120)
    , WeekEnd  = convert(varchar(10),convert(date,(
        case when datepart(week,DateValue) =53 
          then convert(date, (datename(year,DateValue) +'1231')) 
        else dateadd(day,(@@datefirst)-datepart(WeekDay,DateValue)+([email protected]@datefirst),DateValue)
        end) ),120)
    , week= datepart(week,DateValue)

  from dates
)
select *
  , dayname= datename(weekday,date) 
  , weekstartdayname= datename(weekday,weekstart) 
  , weekenddayname= datename(weekday,weekend) 
from test
where week > 51
   or week < 3
order by 1

natijalari:

+------------+------------+------------+------+-----------+------------------+----------------+
|    Date    | WeekStart  |  WeekEnd   | week |  dayname  | weekstartdayname | weekenddayname |
+------------+------------+------------+------+-----------+------------------+----------------+
| 2016-12-27 | 2016-12-26 | 2016-12-31 |   53 | Tuesday   | Monday           | Saturday       |
| 2016-12-28 | 2016-12-26 | 2016-12-31 |   53 | Wednesday | Monday           | Saturday       |
| 2016-12-29 | 2016-12-26 | 2016-12-31 |   53 | Thursday  | Monday           | Saturday       |
| 2016-12-30 | 2016-12-26 | 2016-12-31 |   53 | Friday    | Monday           | Saturday       |
| 2016-12-31 | 2016-12-26 | 2016-12-31 |   53 | Saturday  | Monday           | Saturday       |
| 2017-01-01 | 2017-01-01 | 2017-01-01 |    1 | Sunday    | Sunday           | Sunday         |
| 2017-01-02 | 2017-01-02 | 2017-01-08 |    2 | Monday    | Monday           | Sunday         |
| 2017-01-03 | 2017-01-02 | 2017-01-08 |    2 | Tuesday   | Monday           | Sunday         |
| 2017-01-04 | 2017-01-02 | 2017-01-08 |    2 | Wednesday | Monday           | Sunday         |
| 2017-01-05 | 2017-01-02 | 2017-01-08 |    2 | Thursday  | Monday           | Sunday         |
| 2017-01-06 | 2017-01-02 | 2017-01-08 |    2 | Friday    | Monday           | Sunday         |
| 2017-01-07 | 2017-01-02 | 2017-01-08 |    2 | Saturday  | Monday           | Sunday         |
| 2017-01-08 | 2017-01-02 | 2017-01-08 |    2 | Sunday    | Monday           | Sunday         |
| 2017-12-18 | 2017-12-18 | 2017-12-24 |   52 | Monday    | Monday           | Sunday         |
| 2017-12-19 | 2017-12-18 | 2017-12-24 |   52 | Tuesday   | Monday           | Sunday         |
| 2017-12-20 | 2017-12-18 | 2017-12-24 |   52 | Wednesday | Monday           | Sunday         |
| 2017-12-21 | 2017-12-18 | 2017-12-24 |   52 | Thursday  | Monday           | Sunday         |
| 2017-12-22 | 2017-12-18 | 2017-12-24 |   52 | Friday    | Monday           | Sunday         |
| 2017-12-23 | 2017-12-18 | 2017-12-24 |   52 | Saturday  | Monday           | Sunday         |
| 2017-12-24 | 2017-12-18 | 2017-12-24 |   52 | Sunday    | Monday           | Sunday         |
| 2017-12-25 | 2017-12-25 | 2017-12-31 |   53 | Monday    | Monday           | Sunday         |
| 2017-12-26 | 2017-12-25 | 2017-12-31 |   53 | Tuesday   | Monday           | Sunday         |
| 2017-12-27 | 2017-12-25 | 2017-12-31 |   53 | Wednesday | Monday           | Sunday         |
| 2017-12-28 | 2017-12-25 | 2017-12-31 |   53 | Thursday  | Monday           | Sunday         |
| 2017-12-29 | 2017-12-25 | 2017-12-31 |   53 | Friday    | Monday           | Sunday         |
| 2017-12-30 | 2017-12-25 | 2017-12-31 |   53 | Saturday  | Monday           | Sunday         |
| 2017-12-31 | 2017-12-25 | 2017-12-31 |   53 | Sunday    | Monday           | Sunday         |
| 2018-01-01 | 2018-01-01 | 2018-01-07 |    1 | Monday    | Monday           | Sunday         |
| 2018-01-02 | 2018-01-01 | 2018-01-07 |    1 | Tuesday   | Monday           | Sunday         |
| 2018-01-03 | 2018-01-01 | 2018-01-07 |    1 | Wednesday | Monday           | Sunday         |
| 2018-01-04 | 2018-01-01 | 2018-01-07 |    1 | Thursday  | Monday           | Sunday         |
| 2018-01-05 | 2018-01-01 | 2018-01-07 |    1 | Friday    | Monday           | Sunday         |
| 2018-01-06 | 2018-01-01 | 2018-01-07 |    1 | Saturday  | Monday           | Sunday         |
| 2018-01-07 | 2018-01-01 | 2018-01-07 |    1 | Sunday    | Monday           | Sunday         |
| 2018-01-08 | 2018-01-08 | 2018-01-14 |    2 | Monday    | Monday           | Sunday         |
| 2018-01-09 | 2018-01-08 | 2018-01-14 |    2 | Tuesday   | Monday           | Sunday         |
| 2018-01-10 | 2018-01-08 | 2018-01-14 |    2 | Wednesday | Monday           | Sunday         |
| 2018-01-11 | 2018-01-08 | 2018-01-14 |    2 | Thursday  | Monday           | Sunday         |
| 2018-01-12 | 2018-01-08 | 2018-01-14 |    2 | Friday    | Monday           | Sunday         |
| 2018-01-13 | 2018-01-08 | 2018-01-14 |    2 | Saturday  | Monday           | Sunday         |
| 2018-01-14 | 2018-01-08 | 2018-01-14 |    2 | Sunday    | Monday           | Sunday         |
| 2018-12-24 | 2018-12-24 | 2018-12-30 |   52 | Monday    | Monday           | Sunday         |
| 2018-12-25 | 2018-12-24 | 2018-12-30 |   52 | Tuesday   | Monday           | Sunday         |
| 2018-12-26 | 2018-12-24 | 2018-12-30 |   52 | Wednesday | Monday           | Sunday         |
| 2018-12-27 | 2018-12-24 | 2018-12-30 |   52 | Thursday  | Monday           | Sunday         |
| 2018-12-28 | 2018-12-24 | 2018-12-30 |   52 | Friday    | Monday           | Sunday         |
| 2018-12-29 | 2018-12-24 | 2018-12-30 |   52 | Saturday  | Monday           | Sunday         |
| 2018-12-30 | 2018-12-24 | 2018-12-30 |   52 | Sunday    | Monday           | Sunday         |
| 2018-12-31 | 2018-12-31 | 2018-12-31 |   53 | Monday    | Monday           | Monday         |
| 2019-01-01 | 2019-01-01 | 2019-01-06 |    1 | Tuesday   | Tuesday          | Sunday         |
| 2019-01-02 | 2019-01-01 | 2019-01-06 |    1 | Wednesday | Tuesday          | Sunday         |
| 2019-01-03 | 2019-01-01 | 2019-01-06 |    1 | Thursday  | Tuesday          | Sunday         |
| 2019-01-04 | 2019-01-01 | 2019-01-06 |    1 | Friday    | Tuesday          | Sunday         |
| 2019-01-05 | 2019-01-01 | 2019-01-06 |    1 | Saturday  | Tuesday          | Sunday         |
| 2019-01-06 | 2019-01-01 | 2019-01-06 |    1 | Sunday    | Tuesday          | Sunday         |
| 2019-01-07 | 2019-01-07 | 2019-01-13 |    2 | Monday    | Monday           | Sunday         |
| 2019-01-08 | 2019-01-07 | 2019-01-13 |    2 | Tuesday   | Monday           | Sunday         |
| 2019-01-09 | 2019-01-07 | 2019-01-13 |    2 | Wednesday | Monday           | Sunday         |
| 2019-01-10 | 2019-01-07 | 2019-01-13 |    2 | Thursday  | Monday           | Sunday         |
| 2019-01-11 | 2019-01-07 | 2019-01-13 |    2 | Friday    | Monday           | Sunday         |
| 2019-01-12 | 2019-01-07 | 2019-01-13 |    2 | Saturday  | Monday           | Sunday         |
| 2019-01-13 | 2019-01-07 | 2019-01-13 |    2 | Sunday    | Monday           | Sunday         |
| 2019-12-23 | 2019-12-23 | 2019-12-29 |   52 | Monday    | Monday           | Sunday         |
| 2019-12-24 | 2019-12-23 | 2019-12-29 |   52 | Tuesday   | Monday           | Sunday         |
| 2019-12-25 | 2019-12-23 | 2019-12-29 |   52 | Wednesday | Monday           | Sunday         |
| 2019-12-26 | 2019-12-23 | 2019-12-29 |   52 | Thursday  | Monday           | Sunday         |
| 2019-12-27 | 2019-12-23 | 2019-12-29 |   52 | Friday    | Monday           | Sunday         |
| 2019-12-28 | 2019-12-23 | 2019-12-29 |   52 | Saturday  | Monday           | Sunday         |
| 2019-12-29 | 2019-12-23 | 2019-12-29 |   52 | Sunday    | Monday           | Sunday         |
| 2019-12-30 | 2019-12-30 | 2019-12-31 |   53 | Monday    | Monday           | Tuesday        |
| 2019-12-31 | 2019-12-30 | 2019-12-31 |   53 | Tuesday   | Monday           | Tuesday        |
| 2020-01-01 | 2020-01-01 | 2020-01-05 |    1 | Wednesday | Wednesday        | Sunday         |
| 2020-01-02 | 2020-01-01 | 2020-01-05 |    1 | Thursday  | Wednesday        | Sunday         |
| 2020-01-03 | 2020-01-01 | 2020-01-05 |    1 | Friday    | Wednesday        | Sunday         |
| 2020-01-04 | 2020-01-01 | 2020-01-05 |    1 | Saturday  | Wednesday        | Sunday         |
| 2020-01-05 | 2020-01-01 | 2020-01-05 |    1 | Sunday    | Wednesday        | Sunday         |
| 2020-01-06 | 2020-01-06 | 2020-01-12 |    2 | Monday    | Monday           | Sunday         |
| 2020-01-07 | 2020-01-06 | 2020-01-12 |    2 | Tuesday   | Monday           | Sunday         |
| 2020-01-08 | 2020-01-06 | 2020-01-12 |    2 | Wednesday | Monday           | Sunday         |
| 2020-01-09 | 2020-01-06 | 2020-01-12 |    2 | Thursday  | Monday           | Sunday         |
| 2020-01-10 | 2020-01-06 | 2020-01-12 |    2 | Friday    | Monday           | Sunday         |
| 2020-01-11 | 2020-01-06 | 2020-01-12 |    2 | Saturday  | Monday           | Sunday         |
| 2020-01-12 | 2020-01-06 | 2020-01-12 |    2 | Sunday    | Monday           | Sunday         |
| 2020-12-21 | 2020-12-21 | 2020-12-27 |   52 | Monday    | Monday           | Sunday         |
| 2020-12-22 | 2020-12-21 | 2020-12-27 |   52 | Tuesday   | Monday           | Sunday         |
| 2020-12-23 | 2020-12-21 | 2020-12-27 |   52 | Wednesday | Monday           | Sunday         |
| 2020-12-24 | 2020-12-21 | 2020-12-27 |   52 | Thursday  | Monday           | Sunday         |
| 2020-12-25 | 2020-12-21 | 2020-12-27 |   52 | Friday    | Monday           | Sunday         |
| 2020-12-26 | 2020-12-21 | 2020-12-27 |   52 | Saturday  | Monday           | Sunday         |
| 2020-12-27 | 2020-12-21 | 2020-12-27 |   52 | Sunday    | Monday           | Sunday         |
| 2020-12-28 | 2020-12-28 | 2020-12-31 |   53 | Monday    | Monday           | Thursday       |
| 2020-12-29 | 2020-12-28 | 2020-12-31 |   53 | Tuesday   | Monday           | Thursday       |
| 2020-12-30 | 2020-12-28 | 2020-12-31 |   53 | Wednesday | Monday           | Thursday       |
| 2020-12-31 | 2020-12-28 | 2020-12-31 |   53 | Thursday  | Monday           | Thursday       |
+------------+------------+------------+------+-----------+------------------+----------------+

Taqvim va raqamlar jadvali zikr qilish:

1
qo'shib qo'ydi
@SqlZim - Iltimos, DateValue va sana uchun misollarni taqdim eta olasizmi. Rahmat. SSMS ichida ishlaydigan narsa.
qo'shib qo'ydi muallif FrenkyB, manba
@JamesZ Yaxshi ko'z, datename() faqat char-tipga aniq aylantirilishini talab qilmaydi.
qo'shib qo'ydi muallif SqlZim, manba
@FrenkyB Rextester demo qo'shildi.
qo'shib qo'ydi muallif SqlZim, manba
@FrenkyB Mening @@ datefirst hisobimdagi qizarishim bor edi. Endi tuzatildi.
qo'shib qo'ydi muallif SqlZim, manba

Agar siz faqat bir oylik haftalarni bir oyga orqaga qaytarishni xohlasangiz, bu siz xohlagan narsani bajaradi:

create function dbo.udf_weeks_of_month (@fromdate date) 
returns table as return (
with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, dateadd(month, datediff(month, 0, @fromdate )+1, 0))) 
    [DateValue]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto
)
select 
    WeekOfMonth = row_number() over (order by datepart(week,DateValue))
  , Week        = datepart(week,DateValue)
  , WeekStart   = min(DateValue)
  , WeekEnd     = max(DateValue)
from dates
group by datepart(week,DateValue)
);

uni shunday chaqirgan:

set datefirst 1;
select * from dbo.udf_weeks_of_month('20170101');

qaytaradi:

+-------------+------+------------+------------+
| WeekOfMonth | Week | WeekStart  |  WeekEnd   |
+-------------+------+------------+------------+
|           1 |    1 | 2017-01-01 | 2017-01-01 |
|           2 |    2 | 2017-01-02 | 2017-01-08 |
|           3 |    3 | 2017-01-09 | 2017-01-15 |
|           4 |    4 | 2017-01-16 | 2017-01-22 |
|           5 |    5 | 2017-01-23 | 2017-01-29 |
|           6 |    6 | 2017-01-30 | 2017-01-31 |
+-------------+------+------------+------------+

va bu chaqiruv:

select * from dbo.udf_weeks_of_month('february 2017');

qaytaradi:

+-------------+------+------------+------------+
| WeekOfMonth | Week | WeekStart  |  WeekEnd   |
+-------------+------+------------+------------+
|           1 |    6 | 2017-02-01 | 2017-02-05 |
|           2 |    7 | 2017-02-06 | 2017-02-12 |
|           3 |    8 | 2017-02-13 | 2017-02-19 |
|           4 |    9 | 2017-02-20 | 2017-02-26 |
|           5 |   10 | 2017-02-27 | 2017-02-28 |
+-------------+------+------------+------------+

rextester demo: http://rextester.com/VKPQU7936 (eslatma: rextester tarixni qayta formatlaydi)

1
qo'shib qo'ydi

Bunday vaziyatlarda raqamlar/sana/jadvallarni ishlatish juda aqlli. Bunday jadval ko'p hollarda juda yoqimli!

Bu javobda Men yaratadigan va bunday jadvalni to'ldirish uchun yondashuvni ko'rsataman.

Ushbu jadvalni yaratganingizdan so'ng so'rov quyidagicha oddiy:

SELECT n.CalendarDate AS StartOfWeek
      ,DATEADD(DAY,6,n.CalendarDate) AS EndOfWeek
FROM dbo.RunningNumbers AS n
WHERE n.CalendarDate>={d'2017-01-01'} AND n.CalendarDate<{d'2017-02-01'}
  AND n.CalendarWeekDay=1;

hint Such a table can easily be extended with information hardly to compute simply by adding some values into extra columns manually (e.g. holydays, free-of-work days...)

1
qo'shib qo'ydi

Bu mavzu bo'yicha juda ko'p farqlar. Bu boshqa javoblarga qaraganda bir oz qisqartiriladi.

declare @dt date = {fn current_date()};
declare @start_of_year date = datefromparts(year(@dt), 1, 1);

with digits(d) as (
    select 0 union all select 1 union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all select 7
), wks as (
    select
        dateadd(week, d1.d * 8 + d0.d, dateadd(day, 1-datepart(weekday, @start_of_year), @start_of_year)) as week_start,
        d1.d * 8 + d0.d as wk
    from digits as d0 cross join digits as d1
)
select
    case when year(week_start) < year(@dt)
        then @start_of_year else week_start end as week_start,
    case when year(dateadd(day, 6, week_start)) > year(@dt)
        then datefromparts(year(@dt), 12, 31) else dateadd(day, 6, week_start) end as week_end
from wks
where wk between 0 and 53 and year(week_start) = year(@dt)
order by week_start;
1
qo'shib qo'ydi

I think what you are looking for is here ->someone asked on stackoverflow is similar to what you are looking for it.

This is the final version, hope as per your expectations I build on the above link hope this is what you are looking for ( Apologies new to stackoverflow, unformatted t-sql below)

DECLARE @sDate DATETIME,
        @eDate DATETIME

SET @sDate = '2017-03-01'
SET @eDate = DATEADD(DAY,-1, CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) 
+'-' + Cast((DatePart(MONTH,@sdate)+1) AS varchar(2))+ '-1' AS Date))
SET @sDate = CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) +'-' 
+ Cast(DatePart(MONTH,@sdate) AS varchar(2))+ '-1' AS Date) 

DECLARE @startDayOfWeekOffSet int ;
SET @startDayOfWeekOffSet=
  (SELECT DATEPART(DW,@sDate));

DECLARE @DaysToGetFirstSaturday int
SET @DaysToGetFirstSaturday= 7- @startDayOfWeekOffSet 
DECLARE @firstEverStartOfWeekDay Date , @firstWeekEndDay Date
SET @firstEverStartOfWeekDay [email protected];

IF @startDayOfWeekOffSet = 1 -- January
BEGIN
SET @firstEverStartOfWeekDay = @sDate
SET @DaysToGetFirstSaturday = 0
SET @firstWeekEndDay = @sDate END 
ELSE BEGIN IF DATEPART(MONTH,@sDate) > DATEPART(MONTH,@firstEverStartOfWeekDay)
AND @startDayOfWeekOffSet = 7 --FirstSundayNewMonth
BEGIN
SET @firstEverStartOfWeekDay [email protected]
SET @firstWeekEndDay = @sDate END ELSE --NotASundayNewMonth
BEGIN
SET @firstWeekEndDay = DATEADD(DAY,@DaysToGetFirstSaturday,@firstEverStartOfWeekDay);

END END
SELECT @firstWeekEndDay AS firstWeekEndDay,
       @firstEverStartOfWeekDay AS firstEverStartOfWeekDay,
       @DaysToGetFirstSaturday AS DaysToGetFirstSaturday,
       @startDayOfWeekOffSet AS startDayOfWeekOffSet ;

WITH cte AS
  (SELECT 1 AS WeekNum,
          @firstEverStartOfWeekDay StartDate,
          @firstWeekEndDay EndDate
   UNION ALL SELECT WeekNum + 1, --Case when @scenario = 'NewYear' Then
 dateadd(DAY, 1, cte.EndDate) --Else dateadd(ww, 1, cte.StartDate) End as
 StartDate,
 CASE
     WHEN StartDate = EndDate THEN DateAdd(DAY,-1,dateadd(DAY, 8, cte.EndDate))
     ELSE dateadd(ww, 1, cte.EndDate)
 END AS EndDate
   FROM cte
   WHERE dateadd(ww, 1, StartDate)<= @eDate )
SELECT WeekNum,
       CASE
           WHEN DatePart(YEAR, StartDate) < DATEPART(YEAR,@sDate) 
                THEN CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) + '-1-1' AS Date)
           WHEN DatePart(MONTH, StartDate) < DATEPART(MONTH,@sDate) 
                THEN CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) + '-' 
                + Cast(DatePart(MONTH,@sdate) AS varchar(2)) + '-1' AS Date)
           ELSE StartDate
       END AS StartDate,
       CASE
           WHEN DatePart(MONTH, EndDate) > DATEPART(MONTH,@eDate) THEN @eDate
           ELSE EndDate
       END AS EndDate
FROM cte
1
qo'shib qo'ydi
Bu yaxshi ishlaydi - yagona muammo shundaki, yakshanba kuni birinchi kun sifatida qabul qilinadi.
qo'shib qo'ydi muallif FrenkyB, manba
Muammo shundaki, u haftaning etti kunini qaytaradi, bu har doim ham emas.
qo'shib qo'ydi muallif FrenkyB, manba
Salom FrenkyB umid qilamanki, bu siz qidiryapsiz. Agar siz buni hal qilmoqchi bo'lsangiz, iltimos, javob sifatida belgilang. Rahmat.
qo'shib qo'ydi muallif CodeEnhancer, manba
Ikkita stsenariy uchun ishlashni kuchaytirish 1. Yangi yil - Jan'2017 2. Yangi oy - Yakshanba - Aprel'2017 @FrenkyB - Hafta 7 kun o'rniga 5 kun bo'lishi kerakmi? Agar fevral -2017 va mart -2017 yilgi stsenariylarni qabul qilsam, uni 7 kun davomida hal qilish uchun
qo'shib qo'ydi muallif CodeEnhancer, manba
@FrenkyB - Men o'ylashim mumkin bo'lgan barcha mumkin bo'lgan senaristlar uchun harakat qilmoqda. Bir nechta tahrirlar uchun uzr so'raymiz (sizga bir nechta bildirishnoma berilishi mumkin).
qo'shib qo'ydi muallif CodeEnhancer, manba