Next Quarter Dates

Question

Next Quarter Dates

meichmann

SSCommitted

Points: 1900
More actions
July 19, 2017 at 7:16 am

#331881

Hello Everyone!
Can someone please tell me the expression to get the start and end dates for the next quarter?
Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +1, 0) AS QStart, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +2, 0)) AS QEnd;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

meichmann SSCommitted Points: 1900 More actions · Answer 2

Thom A - Wednesday, July 19, 2017 7:21 AM
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +1, 0) AS QStart, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +2, 0)) AS QEnd;

Well, that would work if I was using SQL, but I need the expressions in SSRS.

Thom A SSC Guru Points: 99126 More actions · Answer 3

meichmann - Wednesday, July 19, 2017 7:27 AM
Thom A - Wednesday, July 19, 2017 7:21 AM
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +1, 0) AS QStart, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +2, 0)) AS QEnd;
Well, that would work if I was using SQL, but I need the expressions in SSRS.

SSRS uses the same syntax, just a little adjustment:
=DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, CDate("01/01/2000"), Today())+1,CDate("01/01/2000"))

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

meichmann SSCommitted Points: 1900 More actions · Answer 4

Thom A - Wednesday, July 19, 2017 7:52 AM
meichmann - Wednesday, July 19, 2017 7:27 AM
Thom A - Wednesday, July 19, 2017 7:21 AM
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +1, 0) AS QStart, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +2, 0)) AS QEnd;
Well, that would work if I was using SQL, but I need the expressions in SSRS.
SSRS uses the same syntax, just a little adjustment:
=DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, CDate("01/01/2000"), Today())+1,CDate("01/01/2000"))

Could you adjust it for the last day please?

meichmann SSCommitted Points: 1900 More actions · Answer 5

meichmann - Wednesday, July 19, 2017 8:13 AM
Thom A - Wednesday, July 19, 2017 7:52 AM
meichmann - Wednesday, July 19, 2017 7:27 AM
Thom A - Wednesday, July 19, 2017 7:21 AM
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +1, 0) AS QStart, DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) +2, 0)) AS QEnd;
Well, that would work if I was using SQL, but I need the expressions in SSRS.
SSRS uses the same syntax, just a little adjustment:
=DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, CDate("01/01/2000"), Today())+1,CDate("01/01/2000"))
Could you adjust it for the last day please?

I got it....

Dateadd(DateInterval.Day, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, CDate("01/01/2000"), Today()) +2, Cdate("01/01/2000")))

Thanks for your help!!! 🙂

Thom A SSC Guru Points: 99126 More actions · Answer 6

meichmann - Wednesday, July 19, 2017 8:19 AM
I got it....
Dateadd(DateInterval.Day, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, CDate("01/01/2000"), Today()) +2, Cdate("01/01/2000")))
Thanks for your help!!! 🙂

Should be +1 not +2. If my "in head" date maths is correct, that would give you a value of 31 March 2018, not 31 December 2017 (which I assume if you goal?).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

meichmann SSCommitted Points: 1900 More actions · Answer 7

Thom A - Wednesday, July 19, 2017 8:23 AM
meichmann - Wednesday, July 19, 2017 8:19 AM
I got it....
Dateadd(DateInterval.Day, -1, DateAdd(DateInterval.Quarter, DateDiff(DateInterval.Quarter, CDate("01/01/2000"), Today()) +2, Cdate("01/01/2000")))
Thanks for your help!!! 🙂
Should be +1 not +2. If my "in head" date maths is correct, that would give you a value of 31 March 2018, not 31 December 2017 (which I assume if you goal?).

Nope, it works fine:) :

Thom A SSC Guru Points: 99126 More actions · Answer 8

meichmann - Wednesday, July 19, 2017 8:27 AM

Nope, it works fine:) :

[/quote]

Yep, you're right, my bad date math. Ha! 🙂 I feel especially silly, as I had +2 in my T-SQL answer. :blush: :hehe:

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

meichmann SSCommitted Points: 1900 More actions · Answer 9

Thom A - Wednesday, July 19, 2017 8:35 AM
meichmann - Wednesday, July 19, 2017 8:27 AM
Nope, it works fine:) :

Yep, you're right, my bad date math. Ha! 🙂 I feel especially silly, as I had +2 in my T-SQL answer. :blush: :hehe:

[/quote]

🙂

Ok, how about first and last day of the previous year?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

meichmann - Wednesday, July 19, 2017 9:09 AM
Thom A - Wednesday, July 19, 2017 8:35 AM
meichmann - Wednesday, July 19, 2017 8:27 AM
Nope, it works fine:) :
Yep, you're right, my bad date math. Ha! 🙂 I feel especially silly, as I had +2 in my T-SQL answer. :blush: :hehe:

🙂

Ok, how about first and last day of the previous year?[/quote]
You have the code for next quarter, it is just a matter of making a few changes to what you already have. Play with it, you will learn more that way rather than just asking others.

meichmann SSCommitted Points: 1900 More actions · Answer 11

Lynn Pettis - Wednesday, July 19, 2017 11:45 AM
meichmann - Wednesday, July 19, 2017 9:09 AM
Thom A - Wednesday, July 19, 2017 8:35 AM
meichmann - Wednesday, July 19, 2017 8:27 AM
Nope, it works fine:) :
Yep, you're right, my bad date math. Ha! 🙂 I feel especially silly, as I had +2 in my T-SQL answer. :blush: :hehe:
🙂
Ok, how about first and last day of the previous year?

You have the code for next quarter, it is just a matter of making a few changes to what you already have. Play with it, you will learn more that way rather than just asking others.

[/quote]

I did and got it, thanks

Thom A SSC Guru Points: 99126 More actions · Answer 12

meichmann - Wednesday, July 19, 2017 11:58 AM
I did and got it, thanks

Glad to hear it!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk