We help IT Professionals succeed at work.
Get Started

Dates Variables

48 Views
Last Modified: 2020-02-20
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.
Comment
Watch Question
Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE