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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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) A socialist is someone who will give you the shirt off *someone else's* back.
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
ShortMonthNameMonthStartMonthend
Jan2018-12-312019-01-27
Feb2019-01-282019-02-24
Mar2019-02-252019-03-31
Apr2019-04-012019-04-28
May2019-04-292019-05-26
Jun2019-05-272019-06-30
Jul2019-07-012019-07-28
Aug2019-07-292019-08-25
Sep2019-08-262019-09-29
Oct2019-09-302019-10-27
Nov2019-10-282019-11-24
Dec2019-11-252019-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