Link to home
Start Free TrialLog in
Avatar of Abi Sa
Abi Sa

asked on

Month - Quarter - Start and End Days calculation dynamically.. Is it possible?

Hi All,

In a table I have a date column, I want to calculate Month and Quarter start days and End days dynamically whenever I load the table or append new records. I have attached the excel on the same. In the sample I have given a series of sorted date but in my actual, it is not sorted.
Month-Quart-Start-End.xlsx
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Try this:
DECLARE @Today DATE = GETDATE()

SELECT @Today AS Today, -- Today
	DATEADD(day,1,EOMONTH(@Today,-1)) AS BofMonth, -- Begin of Month
	EOMONTH(@Today) AS EofMonth, -- End of Month
	DATEPART(qq,@Today) AS Quarter

Open in new window

Or if you want the begin and end of the quarter:
DECLARE @Today DATE = GETDATE()

SELECT @Today AS Today, -- Today
	DATEADD(day,1,EOMONTH(@Today,-1)) AS BofMonth, -- Begin of Month
	EOMONTH(@Today) AS EofMonth, -- End of Month
	DATEADD(qq, DATEDIFF(qq, 0, @Today), 0) AS BofQuarter, -- Begin of Quarter
	DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, @Today)+1, 0)) AS EofQuarter -- End of Quarter

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Vitor solution does not provide what the author was looking for..
Vitor solution does not provide what the author was looking for..
Pawan, can you justify this statement? My query is returning the required dates.
Your solution only returns Y and N when the author required for dates:
"I want to calculate Month and Quarter start days and End days dynamically "
Yes.

Read this - "In a table I have a date column, I want to calculate Month and Quarter start days and End days dynamically whenever I load the table or append new records. ".
That's what I said above. The author wants to calculate a date and not to get Boolean values otherwise he would say I want to know if a giving date is begin or start of a month or quarter and I don't think he said that.
No. Check the input excel attached. Dates are already there. In there it is clearly mentioned what the author is looking for.
I took the Excel as a given example for giving days and not the output that the author wanted to have.
Anyway, a moderator will decide what to do with this question. If still having doubts then better to delete it.