We help IT Professionals succeed at work.

# Dates Variables

on
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
Declare @FirstDayOf2ndQtrCurrentYr
Declare @FirstDayOf3rdQtrCurrentYr
Declare @FirstDayOf4thQtrCurrentYr

Declare @LastDayOf1stQtrCurrentYr
Declare @LastDayOf2ndQtrCurrentYr
Declare @LastDayOf3rdQtrCurrentYr
Declare @LastDayOf4thQtrCurrentYr

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

## View Solution Only

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

SELECT @Today, @begin, @end
``````

Commented:
@Eric Moreau:

OMG! It is working.

Thank  you very much for sharing your intelligence and expertise.

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

Commented:

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