I want to calculate weeks of a month starting from 1st of every month and not from Monday. It needs to be 1st. to 7th., 8th. to 14th., 15th. to 21st., 22nd. to 28th., and 29th. till month-end.
So If I pass date as 2015-12-30 00:00, then it should return week start date as 2015-12-29 00:00 And Week End Date as 2016-01-01 00:00
I tried following script, but it is giving me Week start as Monday.
The first step would be to establish the current week of the month, given by the current day of the month divided by 7 (rounded up), Then you can get the day of the start of the current week by multiplying the week number by 7 and adding a day. Finally you can get the date of the week start using the first of the current month and DATEADD(DAY. The stages are:
DECLARE @Date DATE = '2015-12-30';
SELECT WeekNumberOfMonth = CEILING(DATEPART(DAY, @Date) / 7.0),
WeekStartDay = 1 + FLOOR((DATEPART(DAY, @Date) - 1) / 7.0) * 7,
FirstDayOfMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0),
WeekStart = DATEADD(DAY,
(CEILING(DATEPART(DAY, @Date) / 7.0) - 1) * 7,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0));
Which gives:
WeekNumberOfMonth WeekStartDay FirstDayOfMonth WeekStart
--------------------------------------------------------------------
5 29 2015-12-01 2015-12-29
Finally, the weekend requires a case statement to check if it is still in the same month as the week start:
DECLARE @Date DATE = '2015-12-30';
SELECT d.WeekStart,
Weekend = CASE WHEN DATEADD(DAY, 7, WeekStart) > StartOfNextMonth
THEN StartOfNextMonth
ELSE DATEADD(DAY, 7, WeekStart)
END
FROM ( SELECT WeekStart = DATEADD(DAY,
((DATEPART(DAY, @Date) - 1) / 7.0) * 7,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)),
StartOfNextMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0)
) AS d;
Which gives:
WeekStart Weekend
----------------------------
2015-12-29 2016-01-01
Further Testing
SELECT d.[Date],
d.WeekStart,
Weekend = CASE WHEN DATEADD(DAY, 7, WeekStart) > StartOfNextMonth
THEN StartOfNextMonth
ELSE DATEADD(DAY, 7, WeekStart)
END
FROM ( SELECT dt.[Date],
WeekStart = DATEADD(DAY,
(CEILING(DATEPART(DAY, dt.[Date]) / 7.0) - 1) * 7,
DATEADD(MONTH, DATEDIFF(MONTH, 0, dt.[Date]), 0)),
StartOfNextMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, dt.[Date]) + 1, 0)
FROM (VALUES
(CONVERT(DATE, '20151106')),
(CONVERT(DATE, '20151107')),
(CONVERT(DATE, '20151220')),
(CONVERT(DATE, '20151228')),
(CONVERT(DATE, '20151230')),
(CONVERT(DATE, '20160104'))
) dt ([Date])
) AS d;
OUTPUT
Date WeekStart Weekend
-------------------------------------------
2015-11-06 2015-11-01 2015-11-08
2015-11-07 2015-11-01 2015-11-08
2015-12-20 2015-12-15 2015-12-22
2015-12-28 2015-12-22 2015-12-29
2015-12-30 2015-12-29 2016-01-01
2016-01-04 2016-01-01 2016-01-08
You can use DATEADD() on EOMONTH(). E.g. Minus one day from EOMONTH to get how many days in the month.
To calculate our how many weeks in a month, take the EOMONTH and divide this by 7. Removing the remainder by using the FLOOR() function.
DECLARE @date DATETIME = GETDATE();
SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';
SELECT EOMONTH ( @date, -1 ) AS 'Last Month';
GO
Kind of late, but here's what I use:
DECLARE @now datetime = GETDATE();
-- strip the time part from your date
DECLARE @date datetime = CONVERT(date, @now);
-- do the day of week math
DECLARE @start datetime = DATEADD(d, 1 - DATEPART(w, @date), @date),
@end datetime = DATEADD(d, 8 - DATEPART(w, @date), @date);
SELECT @start AS [WeekStart], @now AS [Now], @end AS [WeekEnd];
Just remember that the BETWEEN operator makes an inclusive [@start, @end] date range, so technically you should use @start <= [Date] AND [Date] < @end or subtract 1 millisecond from @end--unless you can live with the 1/86,400,000 chance of an event being recorded at exactly 0 milliseconds of the next week!
