troubleshooting Question

Dates Variables

Avatar of Queennie L
Queennie L asked on
Microsoft SQL ServerSQL
3 Comments1 Solution53 ViewsLast Modified:
Hello Experts,

I know this is just simple to experts but I am struggling to figure this out.

I want to get the @EndDate if the report will run any @TodayDate between First Date and Last Date of any quarters and default @EndDate to "Last Date of any quarters."

@BeginDate will default to Beginning of Year.

Example 1:
Today's Date = 02/20/2020
First Day -1st Quarter  = 1/1/2020
Last Day -1st Quarter  = 3/31/2020
@Begin Date = 1/1/2020
@EndDate = 3/31/2020

Example 2:
Today's Date = 04/05/2020
First Day – 2nd Quarter  = 4/1/2020
Last Day - 2nd Quarter  = 6/30/2020
@Begin Date = 1/1/2020
@EndDate = 6/30/2020

Example 3:
Today's Date = 09/25/2020
First Day – 3rd Quarter  = 7/1/2020
Last Day - 3rd Quarter  = 9/30/2020
@Begin Date = 1/1/2020
@EndDate = 9/30/2020

Example 4:
Today's Date = 10/10/2020
First Day - 4th Quarter  = 10/1/2020
Last Day - 4th Quarter  = 12/31/2020
@Begin Date = 1/1/2020
@EndDate = 12/31/2020

Date Variables:
Declare @TodayDate Date
Set @TodayDate = getdate()
Declare @FirstDayOf1stQtrCurrentYr
Set @FirstDayOf1stQtrCurrentYr = DATEADD(Q,0,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf2ndQtrCurrentYr
Set @FirstDayOf2ndQtrCurrentYr = DATEADD(Q,1,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf3rdQtrCurrentYr
Set @FirstDayOf3rdQtrCurrentYr = DATEADD(Q,2,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))
Declare @FirstDayOf4thQtrCurrentYr
Set @FirstDayOf4thQtrCurrentYr = ,DATEADD(Q,3,DATEADD(YY,DATEDIFF(YY,0,@TodayDate),0))

Declare @LastDayOf1stQtrCurrentYr
Set @LastDayOf1stQtrCurrentYr = DATEADD(QQ,DATEDIFF(QQ,3,@TodayDate)-3,0)-1
Declare @LastDayOf2ndQtrCurrentYr
Set @LastDayOf2ndQtrCurrentYr = DATEADD(QQ,DATEDIFF(QQ,3,@TodayDate)-2,0)-1
Declare @LastDayOf3rdQtrCurrentYr
Set @LastDayOf3rdQtrCurrentYr = DATEADD(QQ,DATEDIFF(QQ,3,@TodayDate)-1,0)-1
Declare @LastDayOf4thQtrCurrentYr
Set @LastDayOf4thQtrCurrentYr = ,DATEADD(QQ,DATEDIFF(QQ,3,@TodayDate)-0,0)-1

Declare @BeginDate
Set @BeginDate = DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)

Declare @EndDate
Set @EndDate = ??? >>>I want this to be the Last Day of Any Quarters based on @TodayDate.

Thank you for all the help.
Éric Moreau
Senior .Net Consultant
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros