May 30, 2019 at 1:37 pm
Hi I have a requirement to calculate the start and end of the working Month,
The criteria is that if the 1st of the Month is a Monday then use that Date otherwise use the last Monday of the previous month.
Example data Below
DECLARE @Dates TABLE(ShortMonthName VARCHAR(3), [MonthStart] DATETIME,[MonthEnd] DATETIME)
INSERT INTO @Dates
(
ShortMonthName,
MonthStart,
MonthEnd
)
VALUES
('Jan','2018-12-31','2019-01-27'),
('Feb','2019-01-28','2019-02-24'),
('Mar','2019-02-25','2019-03-31'),
('Apr','2019-04-01','2019-04-28'),
('May','2019-04-29','2019-05-26'),
('Jun','2019-05-27','2019-06-30'),
('Jul','2019-07-01','2019-07-28'),
('Aug','2019-07-29','2019-08-25'),
('Sep','2019-08-26','2019-09-29'),
('Oct','2019-09-30','2019-10-27'),
('Nov','2019-10-28','2019-11-24'),
('Dec','2019-11-25','2019-12-29')
SELECT * FROM @dates
I want to be able to create a function that will correctly calculate the start and end of the month for any given date, any year.
So far I have come up with this quite crude solution . However its not working quite correctly.
DECLARE @Date DATETIME='2019-01-28'
SELECT @Date =
CASE WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Monday' THEN DATEADD(month, DATEDIFF(month, 0, @Date), 0)
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Tuesday' THEN DATEADD(DAY,-1,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Wednesday' THEN DATEADD(DAY,-2,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Thursday' THEN DATEADD(DAY,-3,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Friday' THEN DATEADD(DAY,-4,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Saturday' THEN DATEADD(DAY,-5,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Sunday' THEN DATEADD(DAY,-6,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
END
SELECT @date
The above example should return '2019-01-28' as it's the start of February, How ever It's returning for the start of January.
Any help would be much appreciated.
Regards,
Simon
May 30, 2019 at 2:48 pm
I would have written the code differently - basically find the first day of next month and then subtract the number of days from there
However I have a feeling there is a better way of writing this
DECLARE @Date DATETIME='2019-01-28'
SELECT CASE WHEN
DATENAME(WEEKDAY , DATEADD(DAY , 1 , EOMONTH(@date))) = 'Monday' THEN @date
WHEN
DATENAME(WEEKDAY , DATEADD(DAY , 1 , EOMONTH(@date))) = 'Tuesday' THEN DATEADD(dd, -1 , DATEADD(DAY , 1 , EOMONTH(@date)))
WHEN
DATENAME(WEEKDAY , DATEADD(DAY , 1 , EOMONTH(@date))) = 'Wednesday' THEN DATEADD(dd, -2 , DATEADD(DAY , 1 , EOMONTH(@date)))
WHEN
DATENAME(WEEKDAY , DATEADD(DAY , 1 , EOMONTH(@date))) = 'Thursday' THEN DATEADD(dd, -3 , DATEADD(DAY , 1 , EOMONTH(@date)))
WHEN
DATENAME(WEEKDAY , DATEADD(DAY , 1 , EOMONTH(@date))) = 'Friday' THEN DATEADD(dd, -4 , DATEADD(DAY , 1 , EOMONTH(@date)))
WHEN
DATENAME(WEEKDAY , DATEADD(DAY , 1 , EOMONTH(@date))) = 'Saturday' THEN DATEADD(dd, -5 , DATEADD(DAY , 1 , EOMONTH(@date)))
WHEN
DATENAME(WEEKDAY , DATEADD(DAY , 1 , EOMONTH(@date))) = 'Sunday' THEN DATEADD(dd, -6 , DATEADD(DAY , 1 , EOMONTH(@date)))
END
May 30, 2019 at 6:46 pm
Two similar methods - one returns a DATE data type and the other returns a DATETIME:
Declare @monthsBack int = 4;
--==== Return a DATETIME data type
Select ActualFirst = dateadd(month, datediff(month, 0, getdate()) - @monthsBack, 0)
, CalcFirst = dateadd(day, -dw.day_number, dateadd(month, datediff(month, 0, getdate()) - @monthsBack, 0))
From (
Values (0, 'Monday')
, (1, 'Tuesday')
, (2, 'Wednesday')
, (3, 'Thursday')
, (4, 'Friday')
, (5, 'Saturday')
, (6, 'Sunday')
) As dw(day_number, day_name)
Where dw.day_name = datename(weekday, dateadd(month, datediff(month, 0, getdate()) - @monthsBack, 0));
--==== Return a DATE data type
Select ActualFirst = dateadd(day, 1, eomonth(getdate(), -(@monthsBack + 1)))
, CalcFirst = dateadd(day, -dw.day_number, dateadd(day, 1, eomonth(getdate(), -(@monthsBack + 1))))
From (
Values (0, 'Monday')
, (1, 'Tuesday')
, (2, 'Wednesday')
, (3, 'Thursday')
, (4, 'Friday')
, (5, 'Saturday')
, (6, 'Sunday')
) As dw(day_number, day_name)
Where dw.day_name = datename(weekday, dateadd(day, 1, eomonth(getdate(), -(@monthsBack + 1))));
If the function you need is going to be called in a query - then create the function as an inline-table valued function. In fact - I would recommend creating it as an iTVF regardless.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 30, 2019 at 7:17 pm
You can simplify the code by moving the first of the month calculation to a CROSS APPLY:
Declare @monthsBack int = 4;
--==== Return a DATETIME data type
Select ActualFirst = m.fom
, CalcFirst = dateadd(day, -dw.day_number, m.fom)
From (
Values (0, 'Monday')
, (1, 'Tuesday')
, (2, 'Wednesday')
, (3, 'Thursday')
, (4, 'Friday')
, (5, 'Saturday')
, (6, 'Sunday')
) As dw(day_number, day_name)
Cross Apply (Select dateadd(month, datediff(month, 0, getdate()) - @monthsBack, 0) As fom) m
Where dw.day_name = datename(weekday, m.fom);
--==== Return a DATE data type
Select ActualFirst = m.fom
, CalcFirst = dateadd(day, -dw.day_number, m.fom)
From (
Values (0, 'Monday')
, (1, 'Tuesday')
, (2, 'Wednesday')
, (3, 'Thursday')
, (4, 'Friday')
, (5, 'Saturday')
, (6, 'Sunday')
) As dw(day_number, day_name)
Cross Apply (Select dateadd(day, 1, eomonth(getdate(), -(@monthsBack + 1))) As fom) m
Where dw.day_name = datename(weekday, m.fom);
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 30, 2019 at 7:39 pm
I'm using recursive CTE (see attached) to create a lookup table.
May 30, 2019 at 8:31 pm
The function code is in the code box. To invoke it, do this:
SELECT * FROM dbo.GetMonthStartAndEndDates(GETDATE())
Or, if using with another table:
SELECT ...
FROM dbo.data_table dt
CROSS APPLY dbo.GetMonthStartAndEndDates(dt.some_date_column) gm
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.GetMonthStartAndEndDates (
@month date /* a date anywhere in the month */
)
RETURNS TABLE
AS
RETURN (
SELECT
DATEADD(DAY, -DATEDIFF(DAY, 0, first_day_of_month) % 7,
first_day_of_month) AS first_day_of_month,
DATEADD(DAY, -DATEDIFF(DAY, 6, last_day_of_month) % 7,
last_day_of_month) AS last_day_of_month
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @month), 0) AS first_day_of_month,
DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @month) + 1, 0)) AS last_day_of_month
) AS calc_month_start_and_end
)
GO
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
May 30, 2019 at 8:42 pm
This is the code from Suprotim Agarwal - another use of recursive CTE
DECLARE
@StartDate DATETIME,
@EndDate DATETIME
SELECT
@StartDate = '01 Jan 2019',
@EndDate = '31 Dec 2019'
-- Populate CTE with the 1st date of every month
;WITH CTE(mth) AS (
SELECT @StartDate mth
UNION ALL
SELECT DATEADD(month,1,mth) FROM CTE
WHERE DATEADD(month,1,mth) <= @EndDate
)
SELECT DATEADD(DAY, 2 +
CASE WHEN DATEPART(dw,mth) > 2 THEN 7
ELSE 0
END
- DATEPART(dw, mth), mth) as 'First Monday'
FROM CTE
May 31, 2019 at 7:15 am
Thanks for all you replies,
However none of the solutions seem to work.
For example if I pass in '2019-01-28'
I would like it to return
Feb 2019-01-28 as monthStart 2019-02-24 as MonthEnd
As '2019-01-28' is the start date for February.
Thanks
May 31, 2019 at 7:23 am
ShortMonthName MonthStart Monthend
Jan 2018-12-31 2019-01-27
Feb 2019-01-28 2019-02-24
Mar 2019-02-25 2019-03-31
Apr 2019-04-01 2019-04-28
May 2019-04-29 2019-05-26
Jun 2019-05-27 2019-06-30
Jul 2019-07-01 2019-07-28
Aug 2019-07-29 2019-08-25
Sep 2019-08-26 2019-09-29
Oct 2019-09-30 2019-10-27
Nov 2019-10-28 2019-11-24
Dec 2019-11-25 2019-12-29
Basically I want to be able to pass in a date and it calculate the Correct month Start and End as in the table above.
The difficulty I think is getting the Correct Month Start when passing in a date >= the Start of the next month that is still in the current month.
May 31, 2019 at 9:32 am
Start with a iTVF that will calculate the dates for the month
CREATE FUNCTION dbo.MonthStartEnd( @DateInMonth date )
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH cteBaseValues AS (
SELECT ThisMonthStartDate = CAST(DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth), 0) AS date)
, ThisMonthStartNum = CAST(DATEDIFF(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth), 0)) %7 AS tinyint)
, NextMonthStartDate = CAST(DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth) +1, 0) AS date)
, NextMonthStartNum = CAST(DATEDIFF(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth) +1, 0)) %7 AS tinyint)
)
SELECT MonthStart = CASE WHEN v.ThisMonthStartNum = 0 THEN v.ThisMonthStartDate
ELSE DATEADD(dd, 0 + CASE WHEN DATEDIFF(dd, 0, v.ThisMonthStartDate) %7 < 0 THEN -7 ELSE 0 END - DATEDIFF(dd, 0, v.ThisMonthStartDate) %7, v.ThisMonthStartDate)
END
, MonthEnd = DATEADD(DD, -1,
CASE WHEN v.NextMonthStartNum = 0 THEN v.NextMonthStartDate
ELSE DATEADD(dd, 0 + CASE WHEN DATEDIFF(dd, 0, v.NextMonthStartDate) %7 < 0 THEN -7 ELSE 0 END - DATEDIFF(dd, 0, v.NextMonthStartDate) %7, v.NextMonthStartDate)
END)
FROM cteBaseValues AS v;
GO
Then simply CROSS APPLY it to your list of dates to get the month start and end dates
WITH cteTestData AS (
SELECT v.*
FROM (VALUES ( 'Jan', '2019-01-15' )
, ( 'Feb', '2019-02-15' )
, ( 'Mar', '2019-03-15' )
, ( 'Apr', '2019-04-15' )
, ( 'May', '2019-05-15' )
, ( 'Jun', '2019-06-15' )
, ( 'Jul', '2019-07-15' )
, ( 'Aug', '2019-08-15' )
, ( 'Sep', '2019-09-15' )
, ( 'Oct', '2019-10-15' )
, ( 'Nov', '2019-11-15' )
, ( 'Dec', '2019-12-15' )
) AS v(mName, mDate)
)
SELECT td.mName
, MonthStart = CASE WHEN td.mDate > mse.MonthEnd THEN mse2.MonthStart ELSE mse.MonthStart END
, MonthEnd = CASE WHEN td.mDate > mse.MonthEnd THEN mse2.MonthEnd ELSE mse.MonthEnd END
FROM cteTestData AS td
CROSS APPLY dbo.MonthStartEnd(td.mDate) AS mse
CROSS APPLY dbo.MonthStartEnd(DATEADD(mm, 1, td.mDate)) AS mse2
ORDER BY td.mDate;
May 31, 2019 at 9:53 am
Thanks Des,
That Table is just static showing the Start and End Months.
It will not be available in Production.
Using your function still gives me incorrect dates when I use the following date
SELECT *
FROM dbo.MonthStartEnd( '2019-01-28' )
Your function returns '2018-12-31' -'2019-01-27'
It should return '2019-01-28' - '2019-02-24'
Regards,
Simon
May 31, 2019 at 10:15 am
Thanks Des, That Table is just static showing the Start and End Months. It will not be available in Production. Using your function still gives me incorrect dates when I use the following date
SELECT *
FROM dbo.MonthStartEnd( '2019-01-28' )Your function returns '2018-12-31' -'2019-01-27' It should return '2019-01-28' - '2019-02-24' Regards, Simon
Hi Simon
I did edit my post, so not sure if you tested before or after the edit.
Note that I call the function twice to cater for the case when the date in the calendar month actually falls into the next fiscal month
May 31, 2019 at 10:25 am
For a single value, you can still call the function twice
DECLARE @TheDate AS date = '2019-01-28';
SELECT MonthStart = CASE WHEN @TheDate > mse.MonthEnd THEN mse2.MonthStart ELSE mse.MonthStart END
, MonthEnd = CASE WHEN @TheDate > mse.MonthEnd THEN mse2.MonthEnd ELSE mse.MonthEnd END
FROM dbo.MonthStartEnd(@TheDate) AS mse
CROSS APPLY dbo.MonthStartEnd(DATEADD(mm, 1, @TheDate)) AS mse2;
May 31, 2019 at 10:53 am
I have modified the proc to do a check and cater for the date that falls into the next month
CREATE FUNCTION dbo.MonthStartEnd2( @DateInMonth date )
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
WITH cteBaseValues AS (
SELECT ThisMonthStartDate = CAST(DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth), 0) AS date)
, ThisMonthStartNum = CAST(DATEDIFF(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth), 0)) %7 AS tinyint)
, NextMonthStartDate = CAST(DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth) +1, 0) AS date)
, NextMonthStartNum = CAST(DATEDIFF(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, @DateInMonth) +1, 0)) %7 AS tinyint)
)
, cteBaseValuesNext AS (
SELECT ThisMonthStartDate = CAST(DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 1, @DateInMonth)), 0) AS date)
, ThisMonthStartNum = CAST(DATEDIFF(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 1, @DateInMonth)), 0)) %7 AS tinyint)
, NextMonthStartDate = CAST(DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 1, @DateInMonth)) +1, 0) AS date)
, NextMonthStartNum = CAST(DATEDIFF(dd, 0, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, 1, @DateInMonth)) +1, 0)) %7 AS tinyint)
)
, cteMonthThis AS (
SELECT MonthStart = CASE WHEN bv.ThisMonthStartNum = 0 THEN bv.ThisMonthStartDate
ELSE DATEADD(dd, 0 + CASE WHEN DATEDIFF(dd, 0, bv.ThisMonthStartDate) %7 < 0 THEN -7 ELSE 0 END
- DATEDIFF(dd, 0, bv.ThisMonthStartDate) %7, bv.ThisMonthStartDate)
END
, MonthEnd = DATEADD(DD, -1,
CASE WHEN bv.NextMonthStartNum = 0 THEN bv.NextMonthStartDate
ELSE DATEADD(dd, 0 + CASE WHEN DATEDIFF(dd, 0, bv.NextMonthStartDate) %7 < 0 THEN -7 ELSE 0 END
- DATEDIFF(dd, 0, bv.NextMonthStartDate) %7, bv.NextMonthStartDate)
END
)
FROM cteBaseValues AS bv
)
, cteMonthNext AS (
SELECT MonthStart = CASE WHEN bv.ThisMonthStartNum = 0 THEN bv.ThisMonthStartDate
ELSE DATEADD(dd, 0 + CASE WHEN DATEDIFF(dd, 0, bv.ThisMonthStartDate) %7 < 0 THEN -7 ELSE 0 END
- DATEDIFF(dd, 0, bv.ThisMonthStartDate) %7, bv.ThisMonthStartDate)
END
, MonthEnd = DATEADD(DD, -1,
CASE WHEN bv.NextMonthStartNum = 0 THEN bv.NextMonthStartDate
ELSE DATEADD(dd, 0 + CASE WHEN DATEDIFF(dd, 0, bv.NextMonthStartDate) %7 < 0 THEN -7 ELSE 0 END
- DATEDIFF(dd, 0, bv.NextMonthStartDate) %7, bv.NextMonthStartDate)
END
)
FROM cteBaseValuesNext AS bv
)
SELECT MonthStart = CASE WHEN @DateInMonth > mt.MonthEnd THEN (SELECT mn.MonthStart FROM cteMonthNext AS mn) ELSE mt.MonthStart END
, MonthEnd = CASE WHEN @DateInMonth > mt.MonthEnd THEN (SELECT mn.MonthEnd FROM cteMonthNext AS mn) ELSE mt.MonthEnd END
FROM cteMonthThis AS mt;
GO
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply