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,DAT EDIFF(YY,0 ,@TodayDat e),0))
Declare @FirstDayOf2ndQtrCurrentYr
Set @FirstDayOf2ndQtrCurrentYr = DATEADD(Q,1,DATEADD(YY,DAT EDIFF(YY,0 ,@TodayDat e),0))
Declare @FirstDayOf3rdQtrCurrentYr
Set @FirstDayOf3rdQtrCurrentYr = DATEADD(Q,2,DATEADD(YY,DAT EDIFF(YY,0 ,@TodayDat e),0))
Declare @FirstDayOf4thQtrCurrentYr
Set @FirstDayOf4thQtrCurrentYr = ,DATEADD(Q,3,DATEADD(YY,DA TEDIFF(YY, 0,@TodayDa te),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.
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
Declare @FirstDayOf2ndQtrCurrentYr
Set @FirstDayOf2ndQtrCurrentYr
Declare @FirstDayOf3rdQtrCurrentYr
Set @FirstDayOf3rdQtrCurrentYr
Declare @FirstDayOf4thQtrCurrentYr
Set @FirstDayOf4thQtrCurrentYr
Declare @LastDayOf1stQtrCurrentYr
Set @LastDayOf1stQtrCurrentYr = DATEADD(QQ,DATEDIFF(QQ,3,@
Declare @LastDayOf2ndQtrCurrentYr
Set @LastDayOf2ndQtrCurrentYr = DATEADD(QQ,DATEDIFF(QQ,3,@
Declare @LastDayOf3rdQtrCurrentYr
Set @LastDayOf3rdQtrCurrentYr = DATEADD(QQ,DATEDIFF(QQ,3,@
Declare @LastDayOf4thQtrCurrentYr
Set @LastDayOf4thQtrCurrentYr = ,DATEADD(QQ,DATEDIFF(QQ,3,
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SET @begin = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @today), 0)
ASKER
OMG! It is working.
Thank you very much for sharing your intelligence and expertise.
I appreciate it.