Avatar of Queennie L
Queennie L
 asked on

Dates Variables

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.
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Éric Moreau

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Queennie L

ASKER
@Eric Moreau:

OMG! It is working.

Thank  you very much for sharing your intelligence and expertise.

I appreciate it.
Scott Pletcher

SET @begin = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @today), 0)


Your help has saved me hundreds of hours of internet surfing.
fblack61