We help IT Professionals succeed at work.

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.
Comment
Watch Question

Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
Here is a sample to get beginning and ending dates of quarters:
DECLARE @Today Date = '02/20/2020'
--DECLARE @Today Date = '04/05/2020'
--DECLARE @Today Date = '09/25/2020'
--DECLARE @Today Date = '10/10/2020'

DECLARE @begin DATE = CAST(YEAR(@Today) AS VARCHAR(4)) +
                       CASE WHEN MONTH(@Today) IN ( 1,  2,  3) THEN '/01/01'
                            WHEN MONTH(@Today) IN ( 4,  5,  6) THEN '/04/01'
                            WHEN MONTH(@Today) IN ( 7,  8,  9) THEN '/07/01'
                            WHEN MONTH(@Today) IN (10, 11, 12) THEN '/10/01'
                       END

DECLARE @end DATE = DATEADD(DAY, -1, DATEADD(MONTH, 3, @begin))

SELECT @Today, @begin, @end

Open in new window

Author

Commented:
@Eric Moreau:

OMG! It is working.

Thank  you very much for sharing your intelligence and expertise.

I appreciate it.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

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