Next Quarter Dates

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

    Thanks!

  • 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

  • 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.

  • 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

  • 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 - 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!!! 🙂

  • 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

  • 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:) :

  • 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

  • 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?

  • 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.

  • 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

  • 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

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

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